SQL · Theory

IFNULL()

Theory

127/439

IFNULL()

IFNULL(marks, 0) returns marks if not NULL, else 0. Two arguments only. COALESCE(a,b,c) walks the list. In SELECT lists IFNULL is the student favourite.

Trap — IFNULL as a WHERE — you usually still want IS NULL for ‘missing’ vs 0.

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

Exam tip

IFNULL(marks, 0). COALESCE(marks, bonus, 0).

IFNULL() — sample query

-- IFNULL()
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;

IFNULL(): create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefIFNULL(x, 0) replaces NULL with 0.
RuleCOALESCE for more than two fallbacks.
TrapTurning NULL into 0 then losing ‘absent’ meaning.

-- IFNULL() 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;

IFNULL()

IFNULL() — sample query

Short notes

Questions

IFNULL()

IFNULL() — sample query

Short notes

Questions