IS NULL

Empty values: IS NULL

Module 2 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

Find the books that have no series, or the ones that do.

An empty value is NULL. Test it with IS NULL or IS NOT NULL, never = NULL.

Next: What NULL means

On your own New data, same idea

Show the title and series of every book that is part of a series.

The database

11 rows

book_id	title	author	genre	year	pages	series
1	Dracula	Bram Stoker	Horror	1897	418	NULL
2	The Fault in Our Stars	John Green	Romance	2012	313	NULL
3	Pride and Prejudice	Jane Austen	Romance	1813	432	NULL
4	Charlotte's Web	E.B. White	Children	1952	184	NULL
5	The Great Gatsby	F. Scott Fitzgerald	Fiction	1925	180	NULL
6	Angels & Demons	Dan Brown	Thriller	2000	616	Robert Langdon
7	Mockingjay	Suzanne Collins	Science Fiction	2010	390	The Hunger Games
8	The Fellowship of the Ring	J.R.R. Tolkien	Fantasy	1954	423	The Lord of the Rings
9	The Time Machine	H.G. Wells	Science Fiction	1895	118	NULL
10	The Hound of the Baskervilles	Arthur Conan Doyle	Mystery	1902	256	Sherlock Holmes
11	Frankenstein	Mary Shelley	Horror	1818	280	NULL

Your query Ctrl + Enter runs it

Hint: click the green edit button on the board, or type it yourself

Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How IS NULL works

What is NULL?

A value that's missing or unknown. It isn't 0 and it isn't empty text: it's no value at all. A book that stands alone has NULL as its series.
Why doesn't = NULL work?

Comparing anything with an unknown gives "unknown", never true. So series = NULL finds nothing, even for rows where series is NULL.
How do I find NULLs?

WHERE series IS NULL
And IS NOT NULL for rows that do have a value.
What happens to NULL in sums?

pages * NULL is NULL. COUNT(series) and SUM skip NULLs, which you'll use in the totals topic.
How is this like Excel?

NULL is like an empty cell, and IS NULL is like ISBLANK. Excel treats an empty cell as 0 in sums; SQL keeps it as unknown.

Other databases

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

Showing a default instead of NULL
Database	How	Note
SQLite (these lessons)	`COALESCE(series, 'none')`	IFNULL(series, ...) works too.
PostgreSQL	`COALESCE(series, 'none')`
MySQL / MariaDB	`COALESCE(series, 'none')`	IFNULL(series, ...) works too.
SQL Server	`COALESCE(series, 'none')`	ISNULL(series, ...) works too.
Oracle	`COALESCE(series, 'none')`	NVL(series, ...) works too. Careful: Oracle stores empty text ('') as NULL.
Sybase ASE	`COALESCE(series, 'none')`	ISNULL(series, ...) works too.

Know someone fighting with a spreadsheet? Send them this page.