NULLIF() — SQL Technical Interview Questions & Answers

NULLIF()

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.

Technical Interview Tip: NULLIF(marks, 0) → NULL when marks is 0.

Code Examples for NULLIF()

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.

How to Explain NULLIF() to an Interviewer

What does NULLIF do?

NULL if the two values are equal Else the first value