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?

So you want your query to return all of the above rows except those with a `regions` value of `unknown`? — Daniel Kelley, Feb 09 '13 at 19:57
@chihwahli . . . I assume the first line of your question is about `IS NULL` not `IN NULL` (which I'm not familiar with). — Gordon Linoff, Feb 09 '13 at 20:08
@ Daniel: wanted it to work, return either... it works now. thanks for the effort. — chihwah li, Feb 09 '13 at 20:46
@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, Feb 09 '13 at 20:51

score 10 · Accepted Answer · answered Feb 09 '13 at 20:05

10

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.

answered Feb 09 '13 at 20:05

Gordon Linoff

1,242,037
58
646
786

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 Feb 09 '13 at 20:36
SELECT empid,firstname,lastname,country,coalesce(region,'unknown') AS regions ,city FROM HR.Employees where coalesce(region, '') = '' 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 Feb 09 '13 at 20:37

score 4 · Answer 2 · edited May 23 '17 at 11:46

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.

flup · Answer 3 · 2013-02-09T20:35:01.130

2

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

edited Feb 09 '13 at 20:35

answered Feb 09 '13 at 20:03

flup

26,937
7
52
74

Many thanks for pointing out it's ANSI, read the wrong pages and thought wrongly. – chihwah li Feb 09 '13 at 20:39

ANSI equivalent of IS NULL

3 Answers3