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?

Solution 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.

Solution 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.

Solution 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).

Comments

[chihwah li]

If there is a better way to filter, do tell.

[Daniel Kelley]

So you want your query to return all of the above rows except those with a regions value of unknown?

[Kermit]

Are you trying to say unknown is NULL?

[flup]

Do you wish to see the NULL rows only?

[Gordon Linoff]

@chihwahli . . . I assume the first line of your question is about IS NULL not IN NULL (which I'm not familiar with).

[chihwah li]

@ Gordon: correct. Thanks.

[chihwah li]

@ Daniel: wanted it to work, return either... it works now. thanks for the effort.

[chihwah li]

@njk : I was trying to filter out the rows with NULLS. Did not know how to continue. But I know now. thanks for asking.

[chihwah li]

I read a few pages on the web, telling me that IS NOT was T-SQL only. But that was wrong. Thanks for the correction. I rewrote my original code, just to try out coalesce:

[chihwah li]

SELECT empid,firstname,lastname,country,coalesce(region,'unknown') AS regions ,city FROM HR.Employees where coalesce(region, '<null>') = '<null>' or region = N'WA' Returns the rows with NULL now. Will archive it and use 'IS NULL' in the future then. Thanks again.

[chihwah li]

Many thanks for pointing out it's ANSI, read the wrong pages and thought wrongly.