Fixing SQL Queries That Return the Wrong Rows: NULLs and Joins

1.Why rows go missing: NULL in WHERE and HAVING

The hardest query bugs to find are the ones that throw no error. The query runs and returns a result that looks reasonable, but rows are missing. The usual cause is NULL. The Databricks NULL semantics reference uses a small person table with seven rows. Two of them, Marry and Albert, have a NULL age. Keep that table in mind for the next question.

Here is why. The comparison operators >, >=, =, < and <= return NULL whenever either operand is NULL. WHERE, HAVING and JOIN keep a row only when the condition is True. A condition that comes out NULL is treated like False, so the row is dropped without any warning.

-- Persons whose age is unknown (`NULL`) are filtered out from the result set.
> SELECT * FROM person WHERE age > 0;
     name age
 -------- ---
 Michelle  30
     Fred  50
     Mike  18
      Dan  50
      Joe  30

To keep those rows, say so explicitly. Add OR age IS NULL to the condition. IS NULL belongs to the group of expressions designed to handle NULL input. It returns True for a NULL value, so the OR branch is True for Marry and Albert, and all seven rows come back.

-- `IS NULL` expression is used in disjunction to select the persons
-- with unknown (`NULL`) records.
> SELECT * FROM person WHERE age > 0 OR age IS NULL;

The logical operators follow the same three-valued rules. TRUE OR NULL is True, but FALSE OR NULL is NULL. TRUE AND NULL is NULL, but FALSE AND NULL is False. NOT NULL is still NULL. So wrapping a comparison in NOT does not bring back NULL rows: WHERE NOT (age > 18) also drops Marry and Albert. HAVING behaves the same way. In the reference example GROUP BY age HAVING max(age) > 18, the group whose age is NULL is skipped.

Sources1

2.Matching NULL to NULL: the null-safe <=> operator

The same rule causes problems with equality. NULL = NULL evaluates to NULL, not True. A join or self-join on a column that can be NULL therefore never matches rows where that column is NULL. In the reference self-join of person on p1.age = p2.age AND p1.name = p2.name, only five rows come back, because Marry and Albert do not match even themselves.

When <=> replaces = on the age comparison, the same self-join returns all seven rows, and Albert and Marry now pair with themselves. Only make this change if NULL should really count as a match. If unknown values should not be paired, = already gives the right answer.

> SELECT * FROM person p1, person p2
    WHERE p1.age  ?  p2.age
    AND p1.name = p2.name;

Sources1

3.Wrong join type, missing join condition

The other common reason for a wrong row count is the join itself. If you write JOIN with no qualifier, you get an INNER join, which keeps only rows that match on both sides. When the question says "all employees, with their department if known", an inner join silently drops every employee without a matching department. Each join type answers a different question:

The reference example has six employees and three departments. An inner join returns three rows. Switching to LEFT JOIN returns all six employees, with NULL deptname for Chloe, Evan and Amy, whose departments 5, 4 and 6 do not exist:

-- Use employee and department tables to demonstrate left join.
> SELECT id, name, employee.deptno, deptname
   FROM employee
   LEFT JOIN department ON employee.deptno = department.deptno;
 105 Chloe      5        NULL
 103  Paul      3 Engineering
 101  John      1   Marketing
 102  Lisa      2       Sales
 104  Evan      4        NULL
 106   Amy      6        NULL

To find employees *without* a department, don't write a LEFT JOIN and then filter on NULL. An ANTI JOIN does exactly that: it returns Chloe, Evan and Amy, with only the employee columns. SEMI JOIN is the opposite: it returns the matching employees without adding any department columns.

No error is raised. When the join criteria are left out, any join type behaves as a CROSS JOIN, so every employee is paired with every department and you get 6 × 3 = 18 rows. When a join suddenly returns far more rows than either table has, check whether the condition is missing. If you join with USING (column), the column must exist in both tables, or Databricks raises UNRESOLVED_USING_COLUMN_FOR_JOIN. SELECT * with USING shows each join column only once.

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A filter such as WHERE age > 0 keeps rows where age is NULL, because NULL is not a failing value.Why is that wrong?

   A comparison with NULL returns NULL, and WHERE keeps only rows whose condition is True, so NULL rows are dropped unless you add OR age IS NULL.

   Covered in Why rows go missing: NULL in WHERE and HAVING

2. 2.Joining on a.key = b.key will match rows where both keys are NULL.Why is that wrong?

   NULL = NULL evaluates to NULL, so those rows are filtered out by the join. Only the null-safe <=> returns True for two NULLs.

   Covered in Matching NULL to NULL: the null-safe <=> operator

3. 3.A JOIN without an ON clause raises a syntax error, so a missing condition is easy to spot.Why is that wrong?

   Leaving out the join criteria is valid SQL. It turns any join into a CROSS JOIN and silently multiplies the rows.

   Covered in Wrong join type, missing join condition