ANSI equivalent of IS NULL

Question

I am trying to find the ANSI way to write the T-SQL 'IS NULL'. (corrected, was 'IN NULL') Some posts on the internet say you can use coalesce to make it work like 'IS NULL'

The reason I like to do this: portable code. And the query must return the rows that are NULL.

So far I created this:

SELECT empid,
       firstname,
       lastname,
       country,
       coalesce(region,'unknown') AS regions ,
       city
FROM HR.Employees

The result set looks like:

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 identified the rows that are NULL, but how do I filter them out of this set?

Answer 1

Both IS NULL and COALESCE are ANSI standard and available in almost all reasonable databases. The construct that you want, I think, is:

where region IS NULL

This is standard syntax.

To have COALESCE work like IS NULL requires a value that you know is not in the data:

where coalesce(region, '<null>') <> '<null>'

However, you would need different values for dates and numbers.

Answer 2

You seem to be confusing IS NULL (a predicate that checks to see if a value is null) and the T-SQL specific function ISNULL(value, replace) (no space and parameters after it), which is similar, but not identical to COALESCE.

Please see SQL - Difference between COALESCE and ISNULL? for details on how COALESCE and ISNULL differ for T-SQL.

Minor differences like what type is returned and what happens when all the arguments are null aside, 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-null argument (it can take more than two).

As a result, each of these might be used to solve your problem in different ways and with slightly different results.

Answer 3

IS NULL is valid ANSI SQL-92, is called the null predicate.

<null predicate> ::= <row value constructor> IS [ NOT ] NULL

See SQL-92, paragraph 8.6.

So WHEREcolumn nameIS NULL is perfectly valid.

The bit where ANSI SQL treats NULL values different from T-SQL is when you write WHERE column name = NULL or WHERE column name <> NULL. See SET ANSI NULLS (Transact-SQL).