After this lesson you can
- Filter rows with comparisons, AND, OR and IN
- Explain why a comparison with NULL is never true
- Use IS NULL instead of = NULL
WHERE decides which rows survive. It runs after FROM and before
SELECT, which is why you can filter on a column you never return.
SELECT name
FROM customers
WHERE country = 'GE' AND credit > 0;
AND binds tighter than OR, so parenthesise the moment you mix them.
WHERE a AND b OR c almost never means what the person typing it meant.
Try it
CREATE TABLE customers ( id int PRIMARY KEY, name text NOT NULL, country text NOT NULL, credit int);INSERT INTO customers VALUES (1, 'Nino Beridze', 'GE', 500), (2, 'Ana Kapanadze', 'GE', 1200), (3, 'Luka Meladze', 'DE', 0), (4, 'Mari Tsiklauri', 'PL', 300), (5, 'Giorgi Abashidze', 'GE', NULL);SELECT name, country, creditFROM customersWHERE country IN ('GE', 'PL') AND credit > 0;NULL is not a value
NULL means unknown. Comparing an unknown to anything gives another
unknown, and a WHERE clause keeps a row only when the answer is true.
So WHERE credit = NULL returns nothing, ever. Not an error, not a warning:
nothing. The same is true of <> NULL.
SELECT name FROM customers WHERE credit IS NULL;
IS NULL and IS NOT NULL are the only comparisons that answer the
question. Reach for them the moment a column is nullable.
This is the single most common SQL mistake in an interview, and the people making it are usually confident. That is what makes it worth remembering.
Try it yourself
2 visible tests · 2 hidden testsTable customers(id, name, country, credit), where credit may be
NULL. Return the name of every customer whose credit is NULL —
not zero, genuinely unknown.
CREATE TABLE customers ( id int PRIMARY KEY, name text NOT NULL, country text NOT NULL, credit int);INSERT INTO customers VALUES (1, 'Nino', 'GE', 500), (2, 'Ana', 'GE', 0), (3, 'Luka', 'DE', NULL), (4, 'Mari', 'PL', NULL);Sign up to check the hidden tests and save your progress. Sign up