Predict the Output / Spot the Bug: NULL, Aliases, HAVING, and SQL Gotchas

• NULL is unknown – any arithmetic or comparison with NULL yields NULL, which is neither true nor false, so WHERE salary = NULL matches nothing.
• Three-valued logic – SQL uses true, false, and unknown; conditions involving NULL evaluate to unknown, which is treated as "not true" by WHERE.
• Alias visibility – aliases defined in SELECT are not visible in WHERE because SQL evaluates WHERE before SELECT.
• HAVING without GROUP BY – treats the entire table as one implicit group and filters the single aggregate.
• NULLIF for safe division – a / NULLIF(b, 0) turns a zero divisor into NULL, preventing a divide-by-zero error.
• NOT IN with NULLs – if the subquery returns any NULL, NOT IN can never be true, so the query silently returns no rows.

What is three-valued logic in SQL?

SQL uses three truth values: true, false, and unknown. Any comparison involving NULL evaluates to unknown, which is treated as "not true" by WHERE and HAVING. This is the root cause of most NULL-related bugs.

SELECT 1 = NULL;        -- Result: NULL (unknown)
SELECT 1 <> NULL;       -- Result: NULL (unknown)
SELECT NULL = NULL;     -- Result: NULL (unknown)
SELECT 100 + NULL;      -- Result: NULL
SELECT NULL AND TRUE;   -- Result: NULL
SELECT NULL OR TRUE;    -- Result: TRUE (one true is enough)

Why does WHERE column = NULL match nothing?

WHERE bonus = NULL evaluates to unknown for every row, and WHERE only keeps rows where the condition is true. Since unknown is not true, zero rows pass. You must use IS NULL instead.

-- WRONG: matches nothing
SELECT * FROM employees WHERE bonus = NULL;
-- Result: 0 rows

-- CORRECT: use IS NULL
SELECT * FROM employees WHERE bonus IS NULL;
-- Returns all employees with no recorded bonus

-- At National Bank of India, this bug hid all fee-waived accounts
SELECT account_id, fee
FROM   accounts
WHERE  fee IS NULL;  -- shows accounts with no fee assigned

What is the NOT IN trap with NULLs?

x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. The last condition is always unknown, and anything AND unknown is either false or unknown, never true. So the entire expression can never be true, and the query silently returns no rows.

-- Setup: blocked_accounts has a NULL entry
INSERT INTO blocked_accounts (account_id) VALUES (1001), (1002), (NULL);

-- WRONG: NOT IN with NULL in subquery returns no rows
SELECT account_id
FROM   transactions
WHERE  account_id NOT IN (
    SELECT account_id FROM blocked_accounts
);
-- Result: 0 rows (silently wrong)

-- CORRECT: use NOT EXISTS
SELECT t.account_id
FROM   transactions t
WHERE  NOT EXISTS (
    SELECT 1 FROM blocked_accounts b
    WHERE  b.account_id = t.account_id
);
-- Returns transactions for non-blocked accounts

Why can you not filter by a SELECT alias in WHERE?

SQL's logical evaluation order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Because WHERE runs before SELECT computes the alias, the alias does not exist yet at that point. Aliases are visible in ORDER BY (which runs last) but not in WHERE.

-- ERROR: alias not visible in WHERE
SELECT salary * 12 AS annual
FROM   employees
WHERE  annual > 1200000;
-- ERROR: column "annual" does not exist

-- CORRECT: repeat the expression
SELECT salary * 12 AS annual
FROM   employees
WHERE  salary * 12 > 1200000;

-- ALTERNATIVE: wrap in a subquery or CTE
WITH emp AS (
    SELECT salary * 12 AS annual FROM employees
)
SELECT * FROM emp WHERE annual > 1200000;

What does HAVING without GROUP BY do?

HAVING without GROUP BY treats the entire table as one implicit group. The aggregate is computed over all rows, and if the HAVING condition is satisfied, the single aggregate row is returned; otherwise, no rows are returned.

-- Treats entire accounts table as one group
SELECT COUNT(*) AS total_accounts
FROM   accounts
HAVING COUNT(*) > 5;
-- If there are 10 accounts: returns 10
-- If there are 3 accounts: returns 0 rows

What is the BETWEEN reversed bounds trap?

WHERE x BETWEEN 100 AND 10 means x >= 100 AND x <= 10, which is logically impossible, so it returns nothing. BETWEEN does not auto-swap the bounds.

-- WRONG: reversed bounds, returns nothing
SELECT * FROM transactions
WHERE amount BETWEEN 100000 AND 10000;
-- Result: 0 rows

-- CORRECT: lower bound first
SELECT * FROM transactions
WHERE amount BETWEEN 10000 AND 100000;

How does NULLIF prevent divide-by-zero?

a / b throws an error when b is zero. NULLIF(b, 0) returns NULL when b equals zero, so the division produces NULL instead of an error. This is the standard safe-division idiom.

-- ERROR: divide by zero
SELECT total_fees / transaction_count FROM accounts;
-- ERROR when transaction_count = 0

-- SAFE: NULLIF turns zero into NULL
SELECT total_fees / NULLIF(transaction_count, 0) AS avg_fee
FROM   accounts;
-- Returns NULL for accounts with zero transactions

-- Even safer: COALESCE wraps the NULL into a default
SELECT COALESCE(
    total_fees / NULLIF(transaction_count, 0),
    0
) AS avg_fee
FROM   accounts;

What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?

These three forms can give three different numbers on the same data.

-- Sample: accounts table
-- account_id | phone
-- -----------+-------
-- 1001       | 9876543210
-- 1002       | 9876543210
-- 1003       | NULL
-- 1004       | 9123456789

SELECT COUNT(*)              AS total_rows,       -- 4
       COUNT(phone)           AS non_null_phones,  -- 3
       COUNT(DISTINCT phone)  AS unique_phones     -- 2
FROM   accounts;

 total_rows | non_null_phones | unique_phones
------------+-----------------+--------------
          4 |               3 |             2

What about SUM ignoring NULL and CROSS JOIN count?

SUM skips NULLs. If every value is NULL, the result is NULL, not zero. A CROSS JOIN produces the Cartesian product: M rows times N rows gives M x N rows.

-- SUM ignores NULLs
SELECT SUM(bonus) FROM employees;
-- If bonuses are: 10000, NULL, 20000 → result is 30000 (not 30000/3)
-- If all bonuses are NULL → result is NULL, use COALESCE(SUM(bonus), 0)

-- CROSS JOIN multiplies row counts
SELECT COUNT(*)
FROM   branches b        -- 5 rows
CROSS JOIN accounts a;   -- 1000 rows
-- Result: 5000 rows (5 x 1000)

What about ORDER BY NULLS FIRST/LAST?

Different databases sort NULLs differently by default. PostgreSQL puts NULLs last on ascending. To get deterministic ordering, specify NULLS FIRST or NULLS LAST explicitly.

-- Deterministic NULL ordering
SELECT account_id, balance
FROM   accounts
ORDER BY balance ASC NULLS LAST;

-- Reverse: NULLs first
SELECT account_id, balance
FROM   accounts
ORDER BY balance DESC NULLS FIRST;

What should a senior engineer know?

The unifying insight behind all these gotchas is that SQL uses three-valued logic (true, false, unknown) and a fixed clause evaluation order (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY). Understanding these two principles explains why aliases are invisible in WHERE, why aggregates cannot go in WHERE, why NULL comparisons are never true, and why NOT IN with NULLs is dangerous.

• COUNT(1) is identical to COUNT(*) – the myth that one is faster is false.
• ORDER BY in a subquery is not guaranteed to survive; the outer query must have its own ORDER BY.
• A LEFT JOIN followed by COUNT(*) returns 1 for unmatched parents; use COUNT(child.id) for zero.
• In PostgreSQL, IS DISTINCT FROM compares two values treating NULLs as equal, useful for NULL-safe comparisons.

What are common interview follow-up questions?

• What does SELECT NULL IN (1, 2, 3) return? (NULL, because NULL is compared to each value and all comparisons are unknown.)
• What is the difference between COALESCE and IFNULL? (COALESCE is standard SQL and accepts multiple arguments; IFNULL is MySQL-specific with two arguments.)
• Why does WHERE 1 = 1 always pass? (Because 1 equals 1 is true, and it is used as a no-op predicate for dynamic SQL.)