Questions tagged [isnull]
22 questions
23
votes
2 answers
Why is COALESCE not a function?
The docs insist that ISNULL is a function, but COALESCE is not. Specifically, they say
The ISNULL function and the COALESCE expression
If I put on my Lisp hat, I can think of COALESCE as a macro and everything makes sense. However, I've never…
J. Mini
- 1,161
- 8
- 32
8
votes
2 answers
Count NULL values per row
I would like to count the number of null's present per row in a table without enumerating column names. For example:
WITH t as (VALUES
(NULL ,'hi',2,NULL,'null'),
('' ,'hi',2,3,'test'),
(NULL ,'hi',2,3,'null')
)
SELECT countnulls(t)
FROM…
Edward Brown
- 83
- 1
- 5
4
votes
2 answers
Extract rows of a table, that include less than x NULLs
I am working with a SQL Server database, which includes a lot of NULLs.
To analyse my data, I want to extract all rows of the database table, that include less than x NULL marks (e.g. x=2).
My database is similar to this structure:
c1 c2 …
sqlNewie
- 53
- 4
3
votes
2 answers
Avoiding null comparisons in where clause
It's my impression that comparing to null limits the use of an index. We had a DBA come in and that statement was part of his notes, however no better example was given.
If that statement is true, what is a better way to write the following?
SELECT…
rism
- 203
- 2
- 7
2
votes
2 answers
PostgreSQL WHERE IS NULL causes bad query plan
I have a PostgreSQL table with the following schema and indexes
+---------+---------+-----------+----------+--------------+-------------+
| Column | Type | Modifiers | Storage | Stats target | Description…
nick314
- 147
- 8
2
votes
2 answers
Select IDS where all values can't be found in another table
Hey I'm pretty new to SQL and I've come up with a problem that I haven't been able to solve
I'm trying to make a query to bring up ID's that have ALL null values and are located in another table.
DB looks like:
Table 1
BOOK ID| User ID …
Bino
- 23
- 2
2
votes
1 answer
Where NULL IS NULL
I'm seeing a lot of where NULL IS NULL type conditions in code and I'm curious about the overhead that may be involved in this.
So given SELECT * FROM table WHERE field = 'TERM' AND (NULL IS NULL OR anotherField = NULL) on a table > 10+ million…
DecafDb
- 123
- 4
2
votes
1 answer
Addition With Null Values
I have inherited a table that has NULL values for float fields, instead of a 0 value. I am doing some basic testing for querying, and in my tests both ISNULL() and COALESCE() present the same accurate output. In my testing (a table with roughly 5K…
user2676140
- 960
- 3
- 19
- 28
1
vote
3 answers
Matching 3 columns in row - if one column is null then exclude it from comparison
A B C Equal
1 1 1 Y
2 2 NULL N
Hi, I have 3 columns which I need to compare : Columns A,B,C.
If these 3 columns are the same then column 'Equal' = 'Y'
But, if a column is null, then it must be excluded from the comparison, e.g. in…
Peter PitLock
- 1,405
- 4
- 24
- 32
1
vote
1 answer
Displaying null fields as 0 in mySQL select
I have a database that collects statistics from Usenet. When I do a basic select such as:
select day, count(day) from statistics where month = 12 and year = 2020 group by day;
I get the result
+------+------------+
| day | count(day)…
Neil Lombardo
- 11
- 1
1
vote
1 answer
Try_Convert Datetime showing '1/1/1900', want to show space
How do I get try_convert datetime to display NULL when the input is space?
I am getting two different results here. My intention is to show 'space' number query as Null also, just like first query.
select try_convert(datetime,'abcd') --->…
user173948
1
vote
2 answers
Remove all Null values from Sql table at once
I am very new to Teradata and SQL environment. I am trying to remove all the null values from sql table in teradata using
ISNULL(COLUMNNAME,0). But it gives an error that "the data type does not match the defined data type name". Also, would like to…
Kuljeet Keshav
- 115
- 1
- 4
1
vote
1 answer
Difference in efficiency between NVL and IS NULL
I have the below query (showing the last part of the query). In the first example I get a result (which is not exactly as desired) but the results arrive within 10min. The second version results in an error: snapshot too old: rollback segment…
Nickpick
- 177
- 2
- 9
0
votes
2 answers
How can I replace the following query with ISNULL function in SQL?
I have the following query. For performance optimization reasons I have been asked to replace the logic with ISNULL function in SQL. How can I achieve this? please advice.
SELECT CASE
WHEN…
AnOldSoul
- 471
- 1
- 11
- 22
0
votes
1 answer
Improve this statement using ISNULL
Beginner with SQL and trying to improve this using ISNULL instead of the longer statement?
CASE WHEN c.FirstName IS NULL THEN '' ELSE c.FirstName END
Jude
- 1