ANSI equivalent to IS NULL
I am trying to find an ANSI way to write T-SQL "IS NULL". (fixed was "IN NULL") Some posts on the internet say you can use coalesce to make it work like "IS NULL"
The reason I like it: portable code. And the query should return rows that NULL
.
So far I've created this:
SELECT empid,
firstname,
lastname,
country,
coalesce(region,'unknown') AS regions ,
city
FROM HR.Employees
The resulting set looks like this:
empid firstname lastname country regions city
1 Sara Davis USA WA Seattle
2 Don Funk USA WA Tacoma
3 Judy Lew USA WA Kirkland
4 Yael Peled USA WA Redmond
5 Sven Buck UK unknown London
6 Paul Suurs UK unknown London
7 Russell King UK unknown London
8 Maria Cameron USA WA Seattle
9 Zoya Dolgopyatova UK unknown London
I have identified the rows NULL
, but how do I filter them from this set?
source to share
Both IS NULL
and COALESCE
are ANSI standards and are available in almost all intelligent databases. The construction you want, I think, is this:
where region IS NULL
This is the standard syntax.
To COALESCE
work like this IS NULL
requires a value that you know is missing from the data:
where coalesce(region, '<null>') <> '<null>'
However, you will need different values for dates and numbers.
source to share
IS NULL
valid ANSI SQL-92, called null predicate.
<null predicate> ::= <row value constructor> IS [ NOT ] NULL
See SQL-92 clause 8.6.
So the WHERE
column name is IS NULL
fine.
The bit where ANSI SQL handles non-T-SQL NULL values is when you write the column name WHERE
= NULL
or WHERE
column name <> NULL
. See Set ANSI NULLS (Transact-SQL) .
source to share
You seem to be confusing IS NULL
(a predicate that tests if a value is NULL) and a specific T-SQL function ISNULL(value, replace)
(no space and parameters after it), which is similar but not identical before COALESCE
.
For more information on how COALESCE
and ISNULL
differ for T-SQL, see SQL - Difference between COALESCE and ISNULL?
Minor differences, such as the return type and what happens when all arguments are null, ISNULL
is a function that returns the first argument if it is not null, or the second argument if it is. COALESCE
returns the first non-empty argument (can take more than two).
As a result, each of them can be used to solve your problem in different ways and with slightly different results.
source to share