#You Think You Know SQL? Let Me Prove You Wrong in 8 Minutes

5 min read

Mar 17

Think you’ve mastered SQL? Think again! This deep dive into advanced and tricky SQL concepts will challenge your understanding and elevate your database skills in just 8 minutes.

blog thumbnail

Share this article on

Disclaimer

The content provided in this article is based solely on my research and personal understanding. While I strive for accuracy, information may vary, and readers should verify details independently.

If you wish to redistribute or reference this article, please ensure you provide a proper backlink to the original source.

Thank you for your understanding and support!

Level Up Your Tech Knowledge!

Subscribe now to get expert insights, coding tips, and exclusive content delivered straight to your inbox!

By subscribing, you consent to receiving emails from The Cypher Hub

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!

This article was last updated on Mar 17

Comments

SQL is really broad

- Anonymous

Mar 29

Yes you killed it

- Anonymous

Mar 28

This was an indepth deep dive

- Anonymous

Mar 28

Yeah surely l didn't know SQL

- Anonymous

Mar 28

Explore related posts

blog cover

How AI Tools Supercharge Creativity in Software Development—Without Replacing the Human Touch

Imagine you're a solo developer working on a SaaS product idea late into the night. You have a vision for a dashboard that's clean, fast, and intuitive — but you're stuck. Do you create your own authentication from scratch or use Firebase? Should you go serverless or containerized? You fire up ChatGPT for opinions, use Copilot to scaffold a few components, and within an hour, you’ve got two working versions — something that would've taken you an entire weekend before. But something nags at you. Was that your creativity? Or was it AI doing the thinking? This moment captures the quiet tension many developers feel today: Is AI helping us think, or thinking for us? That’s what we’ll explore in this article — how AI tools are transforming the creative process in software development, and how developers can embrace this transformation without losing their creative spark.

3 min read

Apr 7

blog cover

You Think You Know SQL? Let Me Prove You Wrong in 8 Minutes

Think you’ve mastered SQL? Think again! This deep dive into advanced and tricky SQL concepts will challenge your understanding and elevate your database skills in just 8 minutes.

5 min read

Mar 17

Level Up Your Tech Knowledge!

Subscribe now to get expert insights, coding tips, and exclusive content delivered straight to your inbox!

By subscribing, you consent to receiving emails from The Cypher Hub