5 SQL Semantics That Trip Developers Up (And How to Fix Them)

Most developers learn SQL by writing basic SELECT statements until the red squiggle goes away. But SQL isn't a standard procedural language like Python, TypeScript, or Go—it is a declarative language based on relational algebra.

Because of this fundamental difference, it is alarmingly easy to write a query that looks logically sound, executes without throwing a single error, but silently produces completely wrong data or tanks your production database performance.

Here is a breakdown of 5 counter-intuitive SQL semantics that trip developers up every single day, along with how the database engine actually processes them.

---

1. NULL = NULL is NOT True (Three-Valued Logic)

In almost every traditional programming language, equality is binary and reflexive: x == x evaluates to TRUE.

In SQL, this statement returns 0 rows:

-- ❌ WRONG: Always returns an empty set
SELECT * FROM users WHERE middle_name = NULL;

What's Actually Happening?

SQL does not operate on standard two-valued boolean logic (TRUE / FALSE). It uses Three-Valued Logic: TRUE, FALSE, and UNKNOWN.

In SQL, NULL does not mean zero or an empty string—it represents an unknown or missing value.

If Person A's middle name is unknown (NULL), and Person B's middle name is unknown (NULL), are their middle names equal? The database engine doesn't know! Therefore, NULL = NULL evaluates to UNKNOWN.

When a WHERE clause evaluates a row, it only keeps records that resolve strictly to `TRUE. UNKNOWN` is dropped.

The Fix

Always use explicit null-checking operators:

-- ✅ CORRECT: Explicitly checks for missing values
SELECT * FROM users WHERE middle_name IS NULL;

---

2. WHERE vs HAVING: The Filter Order Nobody Explains Right

Junior developers often think WHERE and HAVING are interchangeable filters, with HAVING just being "the one you use with GROUP BY."

Using them interchangeably can cause massive query latency in production:

-- ❌ UNOPTIMIZED: Filtering raw rows INSIDE HAVING
SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id 
HAVING status = 'ACTIVE';

What's Actually Happening?

SQL clauses do not execute top-to-bottom in the order they are written. The logical query execution pipeline runs in this sequence:

1. FROM
2. WHERE (Filters raw rows on disk)
3. GROUP BY (Groups remaining rows)
4. HAVING (Filters aggregated groups)
5. SELECT

When you put a non-aggregate filter inside HAVING, you force the database engine to group every single row in disk memory, compute the aggregations, and then throw away the inactive departments.

The Fix

Filter raw records as early as possible with WHERE so your engine groups a much smaller dataset:

-- ✅ OPTIMIZED: Filter FIRST, aggregate SECOND
SELECT department_id, COUNT(*) 
FROM employees 
WHERE status = 'ACTIVE'
GROUP BY department_id;

Golden Rule: Use WHERE to drop raw records before grouping. Reserve HAVING strictly for aggregate function conditions like COUNT() > 5 or SUM(total) > 1000.

---

3. JOIN vs Subquery: Same Result, Wildly Different Performance

You will often hear developers claim that subqueries and JOINs perform identically because modern query optimizers flatten them. While that holds true for simple subqueries, assuming it for correlated subqueries is dangerous.

-- ⚠️ DANGEROUS: Correlated Subquery
SELECT u.id, u.name 
FROM users u 
WHERE u.id IN (
    SELECT o.user_id 
    FROM orders o 
    WHERE o.total > 500
);

What's Actually Happening?

A correlated subquery references columns from the outer query. If the query optimizer fails to flatten it, the database engine executes the inner subquery once for every single row in the outer table.

If your users table has 100,000 rows, that subquery might run 100,000 separate times! This transforms a linear $O(N)$ set-based operation into an $O(N^2)$ nested-loop bottleneck.

The Fix

Refactor correlated subqueries into set-based JOIN operations:

-- ✅ OPTIMIZED: Set-based Hash/Loop Join
SELECT DISTINCT u.id, u.name 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.total > 500;

---

4. GROUP BY vs DISTINCT: Not Interchangeable

When developers want unique records, they often choose between DISTINCT and GROUP BY based on personal syntax preference.

-- ❌ HEAVY OVERHEAD: Using aggregation memory for simple deduplication
SELECT user_id FROM orders GROUP BY user_id;

What's Actually Happening?

Under the hood, these two operations tell the query engine to perform completely different tasks:

• DISTINCT is a set operation. It tells the engine to sort or hash the final dataset and remove duplicate rows.
• GROUP BY is an aggregation pipeline. It allocates memory buckets to prepare for mathematical metrics (SUM, COUNT, AVG).

If you use GROUP BY without using an aggregate function, you are forcing the database engine to allocate bucket memory for calculations you never intend to run.

The Fix

-- ✅ CLEAN & EFFICIENT: Direct unique set extraction
SELECT DISTINCT user_id FROM orders;

Rule of Thumb: If you aren't calculating metrics across grouped records, do not touch GROUP BY. Use DISTINCT.

---

5. LEFT JOIN + WHERE = Silently Becomes an INNER JOIN

This is the single most common silent bug in data engineering.

Imagine you want a report of all users, including those who have never placed an order:

-- ❌ SILENT BUG: The LEFT JOIN is destroyed by the WHERE clause
SELECT u.id, u.email, o.order_date 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'COMPLETED';

What's Actually Happening?

1. The LEFT JOIN runs correctly and includes users without orders, padding their order columns with NULL.
2. Next, the WHERE clause runs to evaluate WHERE o.status = 'COMPLETED'.
3. For a user with zero orders, o.status is NULL.
4. The expression NULL = 'COMPLETED' evaluates to UNKNOWN.
5. As we learned in Topic 1, UNKNOWN rows are filtered out!

By adding a WHERE condition on the right-hand table, you accidentally wiped out all the NULL-padded rows, silently converting your LEFT JOIN into an `INNER JOIN`.

The Fix

Move right-table conditions directly into the ON clause of the join:

-- ✅ CORRECT: Keeps unmatched users while filtering order criteria
SELECT u.id, u.email, o.order_date 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'COMPLETED';

---

Summary Checklist for Code Reviews

1. Checking for NULLs? Use IS NULL or IS NOT NULL, never = NULL.
2. Filtering rows before grouping? Put the condition in WHERE, not HAVING.
3. Running subqueries in loops? Refactor correlated subqueries to explicit JOINs.
4. Deduplicating simple rows? Use DISTINCT instead of GROUP BY.
5. Filtering a LEFT JOIN? Put right-table predicates in the ON clause to preserve outer rows.

Which of these SQL edge cases has tripped you up in production before? Drop your thoughts or worst database horror stories in the comments below! 🚀

Comments

[Mads Hansen]

Useful checklist. Two examples are worth tightening. u.id IN (SELECT o.user_id FROM orders o WHERE o.total > 500) is not correlated—the inner query never references u. Many optimizers can turn it into a semijoin. Rewriting it as JOIN plus DISTINCT may first create duplicates and then pay to remove them; WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 500) expresses the existence test directly. Also, many engines reject HAVING status = 'ACTIVE' unless status is grouped or aggregated. I’d frame the performance advice as plan-dependent and verify it with EXPLAIN (ANALYZE, BUFFERS) on representative data. Preserve semantics first, then measure.

[Sukriti Chatterjee]

Spot on feedback—thank you for catching these nuances!

You're completely right about the IN example being uncorrelated and WHERE EXISTS being the cleaner semantic for existence tests without the JOIN + DISTINCT memory penalty.

Also a great callout on strict engines rejecting unaggregated columns in HAVING. I've updated the post to reflect true correlated subqueries and added the recommendation to verify execution plans via EXPLAIN ANALYZE. Appreciate you taking the time to share!