SQL: Comparing With Null

• NULL in SQL databases has some sharp edges. For example, mathematical operations on NULL give another NULL (which comes back to us as JavaScript's null).

• >

  exec(`SELECT NULL + 0 AS result`);

  Result:

  Run Code

• >

  exec(`SELECT NULL + 1 AS result`);

  Result:

  [{result: null}]

• >

  exec(`SELECT NULL * 5 AS result`);

  Result:

  [{result: null}]

• When we use = to compare anything with NULL, we get another NULL. That's even true when comparing NULL = NULL.

• >

  exec(`SELECT NULL = NULL AS result`);

  Result:

  [{result: null}]

• >

  exec(`SELECT NULL = 1 AS result`);

  Result:

  [{result: null}]

• >

  exec(`SELECT 'cat' = NULL AS result`);

  Result:

  [{result: null}]

• Fortunately, SQL also has IS NULL and IS NOT NULL comparisons that properly check for NULL values. (As usual, SQLite uses 1 and 0 to represent true and false.)

• >

  exec(`SELECT NULL IS NULL AS result`);

  Result:

  Run Code

• >

  exec(`SELECT NULL IS NOT NULL AS result`);

  Result:

  [{result: 0}]

• >

  exec(`SELECT 5 IS NULL AS result`);

  Result:

  [{result: 0}]

• >

  exec(`SELECT 5 IS NOT NULL AS result`);

  Result:

  [{result: 1}]

• >

  exec(`SELECT (NULL = NULL) IS NULL AS result`);

  Result:

  [{result: 1}]

• Usually, you'll see IS NULL and IS NOT NULL as conditions in WHERE queries.

• For example, suppose that we have a database where only some users have emails. We want to email all of our users. To do that, we need to select all users who have an email address.

• >

  exec(`CREATE TABLE users (name TEXT NOT NULL, email TEXT NULL)`);
  exec(`INSERT INTO users (name, email) VALUES ('Amir', 'amir@example.com')`);
  exec(`INSERT INTO users (name, email) VALUES ('Cindy', NULL)`);
  exec(`SELECT * FROM users WHERE email IS NOT NULL`);

  Result:

  [{name: 'Amir', email: 'amir@example.com'}]

• Here's a code problem:

  Here's a table where some users have a cat, and some don't. Select all users that have a cat.

  9

  1

  2

  3

  4

  5

  6

  7

  exec(`CREATE TABLE users (name TEXT NOT NULL, cat_name TEXT NULL)`);

  exec(`INSERT INTO users (name, cat_name) VALUES ('Amir', 'Ms. Fluff')`);

  exec(`INSERT INTO users (name, cat_name) VALUES ('Betty', 'Keanu')`);

  exec(`INSERT INTO users (name, cat_name) VALUES ('Cindy', NULL)`);

  exec(`INSERT INTO users (name, cat_name) VALUES ('Dalili', NULL)`);

  exec(`INSERT INTO users (name, cat_name) VALUES ('Wilford', 'Wilford')`);

  exec(`

  16

  8

  SELECT * FROM users WHERE cat_name IS NOT NULL

  17

  9

  `);

  Goal:

  [{name: 'Amir', cat_name: 'Ms. Fluff'}, {name: 'Betty', cat_name: 'Keanu'}, {name: 'Wilford', cat_name: 'Wilford'}]

  Yours:

  [{name: 'Amir', cat_name: 'Ms. Fluff'}, {name: 'Betty', cat_name: 'Keanu'}, {name: 'Wilford', cat_name: 'Wilford'}]

  Show Author's Answer

• The specific details of null handling vary from database to database. Fortunately, SQLite's NULL behavior was designed to be similar to other SQL databases, so the specifics above apply to most databases.