> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL IS NULL > How to test for NULL in MySQL with IS NULL and IS NOT NULL, why = NULL always returns nothing, and what the NULL-safe equal operator does instead. All examples on this page work on VillageSQL. Install Now → NULL means the value is not there. It is not 0, and it is not an empty string. It needs its own operator, because the comparison operators you already know cannot see it. Open the client with `mysql -u root -p sakila` before you start. ## How IS NULL works ```sql theme={null} SELECT COLUMN_LIST FROM TABLE_NAME WHERE COLUMN_NAME IS NULL; ``` Replace `COLUMN_NAME` with the column to test. `IS NULL` is true when the column holds no value. `IS NOT NULL` is true when it holds one. Those 2 are the only reliable tests, and the reason is the rule underneath them: any comparison involving NULL gives NULL, which is neither true nor false, so `WHERE` drops the row. ## Examples ### 1) Find the missing values Sakila records the language a film was originally made in, and never fills it in. ```sql theme={null} SELECT title, original_language_id FROM film ORDER BY title LIMIT 3; ``` ```text theme={null} +------------------+----------------------+ | title | original_language_id | +------------------+----------------------+ | ACADEMY DINOSAUR | NULL | | ACE GOLDFINGER | NULL | | ADAPTATION HOLES | NULL | +------------------+----------------------+ 3 rows in set (0.00 sec) ``` The client prints `NULL` where a value would go. ```sql theme={null} SELECT title FROM film WHERE original_language_id IS NULL ORDER BY title LIMIT 3; ``` ```text theme={null} +------------------+ | title | +------------------+ | ACADEMY DINOSAUR | | ACE GOLDFINGER | | ADAPTATION HOLES | +------------------+ 3 rows in set (0.00 sec) ``` ### 2) Find the rows that have a value All 1,000 films leave that column empty, so the opposite test returns nothing. ```sql theme={null} SELECT title FROM film WHERE original_language_id IS NOT NULL ORDER BY title; ``` ```text theme={null} Empty set (0.00 sec) ``` ### 3) A column that is sometimes missing `address2` is the second line of an address. Sakila has 603 addresses, and it records the second line as missing on 4 of them and as an empty string on the other 599. ```sql theme={null} SELECT address_id, address, address2 FROM address WHERE address2 IS NULL ORDER BY address_id; ``` ```text theme={null} +------------+----------------------+----------+ | address_id | address | address2 | +------------+----------------------+----------+ | 1 | 47 MySakila Drive | NULL | | 2 | 28 MySQL Boulevard | NULL | | 3 | 23 Workhaven Lane | NULL | | 4 | 1411 Lillydale Drive | NULL | +------------+----------------------+----------+ 4 rows in set (0.00 sec) ``` ## A missing value and an empty one are different Only 4 of the 603 addresses have no second line recorded. The other 599 hold an empty string, which is a value: somebody wrote nothing there on purpose. ```sql theme={null} SELECT address_id, address2 FROM address WHERE address2 = '' ORDER BY address_id LIMIT 3; ``` ```text theme={null} +------------+----------+ | address_id | address2 | +------------+----------+ | 5 | | | 6 | | | 7 | | +------------+----------+ 3 rows in set (0.00 sec) ``` The client prints `NULL` for a missing value and prints nothing for an empty string, so the 2 look almost alike on screen and behave nothing alike in a query. `IS NULL` finds the first 4 rows and `= ''` finds the other 599. Neither test finds both, so a column that mixes them needs `address2 IS NULL OR address2 = ''` to catch every blank. ## = NULL matches nothing, ever Every film has a NULL in `original_language_id`, so this looks like it should return 1,000 rows. ```sql theme={null} SELECT title FROM film WHERE original_language_id = NULL ORDER BY title; ``` ```text theme={null} Empty set (0.01 sec) ``` No rows, no error, no warning. `=` asks whether 2 values are the same, and NULL is not a value, so the answer is neither yes nor no. It is NULL, and `WHERE` keeps only rows where the condition is true. The same trap catches `<> NULL`, which also matches nothing. `IS NULL` and `IS NOT NULL` are the operators built for the job. One operator escapes the rule. `<=>` is the NULL-safe equal: it always answers 1 or 0, and it counts 2 NULLs as equal. Reach for it when you compare 2 columns that may both be missing and you want that to count as a match. The [Operators](/docs/tutorial/operators) lesson shows it beside the comparisons it replaces. ## Summary * Use `IS NULL` and `IS NOT NULL` to test for a missing value. * Never write `= NULL` or `<> NULL`, because both match no rows and say nothing. * Expect any comparison with NULL to give NULL, which `WHERE` treats as not true. * Use `<=>` when 2 NULLs should count as equal. * Test for an empty string separately, because `IS NULL` does not find one. ## See also * [WHERE](/docs/tutorial/where) — the clause these tests go in * [Operators](/docs/tutorial/operators) — how NULL behaves against the other operators * [IN](/docs/tutorial/in) — why a NULL in a `NOT IN` list returns no rows * [NULL in MySQL](/docs/guides/null-in-mysql) — NULL in joins, aggregates, indexes and constraints