Subqueries, IN, EXISTS and the NULL traps

After joins and GROUP BY, the two topics that separate a pass from a fail in SQL screenings are subqueries and NULL. Subqueries let one query use the result of another. NULL is the value that is not a value, and it breaks ordinary logic in ways that produce wrong answers with no error message.

Subqueries in WHERE

A subquery is a SELECT inside brackets. In WHERE it usually returns one value or one column of values.

Example 1: employees who earn more than the average salary.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The inner query runs once, returns a single number, and the outer query compares against it. If the average is 50,000, this is the same as WHERE salary > 50000.

Example 2: customers who have placed at least one order.

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

The same question with EXISTS:

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

EXISTS checks whether the inner query returns any row at all; the SELECT 1 is a placeholder. For "customers with no orders" use NOT EXISTS, which is safer than NOT IN for the reason below.

How NULL behaves

NULL means unknown. Any comparison with NULL, including = NULL and <> NULL, is neither true nor false, so the row is not returned. Use IS NULL and IS NOT NULL.

Example 3: a table has 10 rows, 3 with NULL in the phone column. WHERE phone <> '0' returns 7 rows, not 10, because the 3 NULL rows fail the test. To include them: WHERE phone <> '0' OR phone IS NULL.

The NOT IN trap: if the subquery list contains even one NULL, NOT IN returns no rows at all. WHERE id NOT IN (SELECT customer_id FROM orders) returns nothing if any order has a NULL customer_id. Add WHERE customer_id IS NOT NULL inside, or use NOT EXISTS.

Aggregates ignore NULL. COUNT(*) counts rows; COUNT(phone) counts non-NULL phones. For the table above, COUNT(*) is 10 and COUNT(phone) is 7. AVG also skips NULLs, so the average of 10, 20 and NULL is 15, not 10. COALESCE(phone, 'none') replaces NULL with a value you choose.

Common mistakes and a practice habit

• Writing = NULL. It never matches.
• Using NOT IN with a column that can be NULL.
• A subquery that returns more than one row where a single value was expected, which raises an error.

Create a tiny table with ten rows and put NULL in two columns. Run COUNT(*), COUNT(column), a not-equal filter, a NOT IN and a NOT EXISTS, and write down the row counts before running each one. The surprises you get now are the ones the test will give you later.