IS NULL — SQL Technical Interview Questions & Answers

IS NULL

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.

Technical Interview Tip: WHERE marks IS NULL; COALESCE(marks, 0).

Code Examples for IS NULL

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.

How to Explain IS NULL to an Interviewer

How do you find missing marks?

WHERE marks IS NULL

Why not = NULL?

NULL means unknown The test is not true