SQL · Theory

IS NULL

Theory

56/439

IS NULL

NULL means unknown / missing. WHERE marks IS NULL; students who have no marks yet. 0 is a real number. '' is an empty string. They are not NULL.

Any maths with NULL becomes NULL: marks + 1 is NULL if marks is NULL. IFNULL(marks, 0) or COALESCE(marks, 0) fills a default in the SELECT.

INSERT without a value into a nullable column stores NULL. NOT NULL columns reject that.

Trap — WHERE marks = NULL — always unknown, returns no rows. Trap: COUNT(marks) skips NULL; COUNT(*) counts rows.

On the example next to this theory: IS NULL: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

WHERE marks IS NULL; COALESCE(marks, 0).

IS NULL — sample query

-- IS NULL
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

IS NULL: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefNULL = unknown. Test with IS NULL.
RuleNever = NULL. COALESCE / IFNULL for a default.
TrapMixing NULL, 0, and ''.

SQL · Theory

IS NULL

← All stacks

Theory

56/439

IS NULL

NULL means unknown / missing. WHERE marks IS NULL; students who have no marks yet. 0 is a real number. '' is an empty string. They are not NULL.

Any maths with NULL becomes NULL: marks + 1 is NULL if marks is NULL. IFNULL(marks, 0) or COALESCE(marks, 0) fills a default in the SELECT.

INSERT without a value into a nullable column stores NULL. NOT NULL columns reject that.

Trap — WHERE marks = NULL — always unknown, returns no rows. Trap: COUNT(marks) skips NULL; COUNT(*) counts rows.

On the example next to this theory: IS NULL: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

WHERE marks IS NULL; COALESCE(marks, 0).

IS NULL — sample query

-- IS NULL
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

IS NULL: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefNULL = unknown. Test with IS NULL.
RuleNever = NULL. COALESCE / IFNULL for a default.
TrapMixing NULL, 0, and ''.

IS NULL

IS NULL — sample query

Short notes

Questions

IS NULL

IS NULL — sample query

Short notes

Questions