The IS NULL Operator






The IS NULL Operator

Use the IS NULL operator to select rows that have no value in a particular column.

<predicate with null> ::=
   <scalar expression> IS [ NOT ] NULL


In Figure, we showed how all players with a league number can be found. This statement can also be formulated in another way, one that corresponds more to the original question.

43. Get the player number and the league number of each player who has a league number.

SELECT   PLAYERNO, LEAGUENO
FROM     PLAYERS
WHERE    LEAGUENO IS NOT NULL

Explanation: Note that the word IS may not be replaced by the equals sign.

If NOT is left out, we get all the players who have no league number.

Figure. Get the name, the number, and the league number of each player whose league number is not equal to 8467.

SELECT   NAME, PLAYERNO, LEAGUENO
FROM     PLAYERS
WHERE    LEAGUENO <> '8467'
OR       LEAGUENO IS NULL

The result is:

NAME       PLAYERNO  LEAGUENO
---------  --------  --------
Everett           2  2411
Wise              7  ?
Newcastle         8  2983
Collins          27  2513
Collins          28  ?
Bishop           39  ?
Baker            44  1124
Brown            57  6409
Hope             83  1608
Miller           95  ?
Parmenter       100  6524
Moorman         104  7060
Bailey          112  1319

If the condition LEAGUENO IS NULL were left out, the result would contain only rows in which the LEAGUENO column is not equal to NULL and not equal to 8467 (see result table below). This is because the value of the condition LEAGUENO <> '8467' is unknown if the LEAGUENO column has the value NULL. The result table is:

NAME       PLAYERNO  LEAGUENO
---------  --------  --------
Everett           2      2411
Newcastle         8      2983
Collins          27      2513
Baker            44      1124
Brown            57      6409
Hope             83      1608
Parmenter       100      6524
Moorman         104      7060
Bailey          112      1319

Imagine that E1 is an expression, then:

E1 IS NOT NULL

is equivalent to:

NOT (E1 IS NULL)

Note

A condition with IS NULL or IS NOT NULL can never have the value unknown; work this out by yourself.


Exercise 8.37:

Get the number of each player who has no league number.

Exercise 8.38:

Why is the condition in the following SELECT statement not useful?

SELECT   *
FROM     PLAYERS
WHERE    NAME IS NULL



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows