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.
Share this article on
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!
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.
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!
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.
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?
By default, ORDER BY DESC
puts NULL
values first because they are considered the “largest” unknown values.
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
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
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
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
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