Imagine this: You're in a technical interview, confidently answering SQL questions. The interviewer asks a seemingly simple query, and you blurt out the answer. But then, they smile and say, "Are you sure?" You rerun the query... and the results surprise you. Suddenly, you're second-guessing everything you thought you knew about SQL.

You're not alone. SQL is deceptive. It looks simple but hides traps that even experienced developers fall into. Let's explore five tricky SQL concepts that might just prove you wrong.

1. NULL Is Not What You Think

Let’s say you run this query:

SELECT * FROM users WHERE age != 30;

You expect it to return everyone except users aged 30. But surprise! Some users are missing. Why? Because NULL values in the age column don’t behave as you expect.

Why?

In SQL, NULL means "unknown." When you compare NULL to anything, the result isn’t TRUE or FALSE—it’s UNKNOWN. That means rows where age is NULL don’t get included at all!

Fix:

Use IS NOT NULL explicitly:

SELECT * FROM users WHERE age IS NULL OR age != 30;

Boom. Now, no missing users.

2. COUNT() Lies (Sometimes)

Quick question: How many users do we have?

SELECT COUNT(age) FROM users;

If you think this counts all users, you’re wrong. COUNT(column) only counts non-null values. If some users have NULL ages, they aren’t counted.

The Right Way:

SELECT COUNT(*) FROM users;

This counts all rows, regardless of NULL values. Always be careful with COUNT()!

3. ORDER BY Can Trick You

You want to get the highest-paying customers first, so you run:

SELECT name, revenue FROM customers ORDER BY revenue DESC;

But wait... why are some NULL revenues appearing at the top?

The Gotcha:

By default, ORDER BY DESC puts NULL values first because they are considered the “largest” unknown values.

The Fix:

Use NULLS LAST explicitly:

SELECT name, revenue FROM customers ORDER BY revenue DESC NULLS LAST;

Now your list looks right.

4. The JOIN Duplicates Trap

You join two tables and suddenly get way more rows than expected. Let’s say we have orders and customers:

SELECT * FROM customers c
JOIN orders o ON c.id = o.customer_id;

You expect each customer to appear once. But suddenly, some customers show up multiple times.

Why?

If a customer has multiple orders, they get repeated once per order! A one-to-many relationship can explode row counts.

The Fix:

If you only need distinct customers, use DISTINCT:

SELECT DISTINCT c.* FROM customers c
JOIN orders o ON c.id = o.customer_id;

Or, better yet, use GROUP BY with aggregates.

5. Recursive CTE Magic

You have an organizational structure where employees report to managers. How do you get all subordinates of a given manager? A simple JOIN won’t work. You need recursion:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 1 -- Start with the top-level manager

    UNION ALL

    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

Boom! You just retrieved an entire hierarchy with one query. Recursive CTEs are SQL sorcery.

6. Window Functions vs. GROUP BY: The Subtle Distinction

Window functions (ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD()) allow calculations across a set of table rows related to the current row without collapsing data like GROUP BY.

SELECT order_id, customer_id, order_date,
       RANK() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rank
FROM orders;

Why It’s Tricky: Unlike GROUP BY, window functions retain row-level granularity, making them extremely useful for ranking, running totals, and trend analysis.

7. The "HAVING" Without GROUP BY Trap

Most developers associate HAVING with GROUP BY, but did you know you can use HAVING without it?

SELECT COUNT(*) AS order_count
FROM orders
HAVING COUNT(*) > 100;

Why It’s Tricky: HAVING acts as a filter for aggregate results, even without GROUP BY, effectively treating the entire dataset as a single group.

8. Indexing Gone Wrong

Indexes speed up queries, but adding too many or the wrong type can degrade performance.

CREATE INDEX idx_user_email ON users(email);

Sounds good, right? But if your queries are mostly range-based (LIKE '%gmail.com'), an index on email won’t help much. Instead, consider:

CREATE INDEX idx_user_email_partial ON users(email) WHERE email LIKE '%gmail.com';

Why It’s Tricky: Not all indexes improve performance. Misusing them can slow down inserts/updates and waste storage.

9. Deadlocks: The Silent Killers

Deadlocks happen when two transactions hold locks on resources the other needs. Consider this scenario:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Meanwhile, another transaction runs:

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Both transactions wait on each other forever. The solution? Always lock rows in a consistent order.

SELECT * FROM accounts WHERE id IN (1,2) FOR UPDATE;

Why It’s Tricky: Deadlocks are hard to spot and can silently crash your system under high load.

The Wrap-Up

SQL is full of hidden traps and powerful tricks. If any of these concepts caught you off guard, don’t worry—you just leveled up. Next time someone says they “know SQL,” test them with these!

Did I prove you wrong? Let’s discuss!