SQL · Theory

NULLIF()

Theory

128/439

NULLIF()

NULLIF(a, b) returns NULL if a = b, else a. NULLIF(marks, 0) turns 0 into NULL so AVG skips zeros if you want that. Opposite-ish of IFNULL.

Trap — NULLIF as a general ‘if’ — use IF/CASE.

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

Exam tip

NULLIF(marks, 0) → NULL when marks is 0.

NULLIF() — sample query

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

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

Short notes

DefNULLIF(a,b) is NULL when a equals b.
RuleTurn a sentinel 0 into NULL.
TrapUsing it instead of CASE.

SQL · Theory

NULLIF()

← All stacks

Theory

128/439

NULLIF()

NULLIF(a, b) returns NULL if a = b, else a. NULLIF(marks, 0) turns 0 into NULL so AVG skips zeros if you want that. Opposite-ish of IFNULL.

Trap — NULLIF as a general ‘if’ — use IF/CASE.

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

Exam tip

NULLIF(marks, 0) → NULL when marks is 0.

NULLIF() — sample query

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

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

Short notes

DefNULLIF(a,b) is NULL when a equals b.
RuleTurn a sentinel 0 into NULL.
TrapUsing it instead of CASE.

NULLIF()

NULLIF() — sample query

Short notes

Questions

NULLIF()

NULLIF() — sample query

Short notes

Questions