# The IS NULL Operator

### The IS NULL Operator

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

 ``` ::= 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 ```