Databases & SQL · Class 12 · Querying with SELECT

MySQLNULL⏱️ 7 min read

IS NULL & IS NOT NULL

The concepts chapter said NULL means “no value here”. Now you need to find those rows — and the = you have been using all chapter will not do it. It will not fail either, which is what makes this worth a lesson of its own.

1Why = NULL finds nothing

Two students have no percentage recorded. Here is the query you would expect to find them:

MySQL command line client

mysql> select * from students where percent = null;

Empty set (0.00 sec)

Empty set. No error, no warning, no hint that anything is wrong — and Bhim and Pema are sitting right there in the table. This is a genuinely dangerous result, because a query that returns nothing looks a lot like a table that contains nothing.

The reason is the rule from the concepts chapter:

MySQL command line client

mysql> select null = null;

+-------------+

| null = null |

+-------------+

| NULL |

+-------------+

1 row in set (0.00 sec)

Comparing with an unknown produces an unknown, not a yes or a no. And a where keeps a row only when its condition comes out true. NULL is not true, so the row is dropped — every time, for every row.

The rule in one line

= compares two values. NULL is the absence of a value, so there is nothing for = to work on. You need an operator that asks a different question: not “are these equal?” but “is this one missing?”

2IS NULL

MySQL command line client

mysql> select * from students where percent is null;

+------+------+-------+---------+------------+---------+

+------+------+-------+---------+------------+---------+

| 2500 | Bhim | 12 | NULL | 2008-06-22 | Singtam |

| 3120 | Pema | 10 | NULL | 2010-03-28 | Namchi |

+------+------+-------+---------+------------+---------+

2 rows in set (0.01 sec)

There they are. is null is a single operator — two words that work as one — and it asks exactly the right question.

3IS NOT NULL

The opposite: rows where a value has been recorded.

MySQL command line client

mysql> select id, name, city from students where city is not null;

+------+--------+----------+

| id | name | city |

+------+--------+----------+

| 1099 | Veena | Singtam |

| 1187 | Anjali | Gangtok |

| 1402 | Lhamu | Namchi |

| 2035 | Ravi | Gangtok |

| 2044 | Karma | Singtam |

| 2199 | Diana | Kolkata |

| 2500 | Bhim | Singtam |

| 3033 | Tom | Kolkata |

| 3120 | Pema | Namchi |

| 3301 | Farhan | Siliguri |

+------+--------+----------+

10 rows in set (0.00 sec)

Ten rows. Sohan and Nima, whose city is NULL, are the two that are missing — and ten plus two is twelve, so between them is null and is not null account for every row in the table. No row escapes both, which is not true of = and <>.

This is where marks are lost

A question asks for “students whose city has not been entered”. Writing where city = NULL or where city = 'NULL' gets no marks. The first finds nothing; the second looks for students living in a town literally called NULL. Only where city is null is right.

4NULL is not the word “NULL”

The resultset prints the marker as the letters NULL, which makes it look like a piece of text sitting in the cell. It is not. Quote it and you are asking about a value that does not exist in this table:

MySQL command line client

mysql> select * from students where percent = "";

Empty set (0.00 sec)

An empty string is a value somebody entered; NULL is the absence of one. Different things, and neither is found by asking for the other.

5Recap

where col is null

Finds the rows where the value is missing.

where col is not null

Finds the rows where a value has been recorded.

where col = null

Always Empty set. No error, which is what makes it dangerous.

Together they cover everything

Every row is caught by exactly one of the two.

Quick Check

Which query lists students whose percentage has not been recorded?

Quick Check

What does select * from students where percent = null; return?

Quick Check

A table has 12 rows and 3 have NULL in city. How many does where city is not null return?

← Previous

📐 BETWEEN & IN

Pattern Matching with LIKE 🔤