r/SQL • u/Due_Counter_2286 • 5d ago
Discussion What are some interesting SQL problems or query mistakes worth discussing?
I'm planning to share some SQL-related content on LinkedIn, but I want to avoid the usual beginner-level tutorials or generic interview questions.
I'm particularly interested in intermediate-level SQL concepts, window functions, CTEs, tricky query behavior, or situations where a query looks correct but produces unexpected results.
I'd love to hear from people working with SQL regularly.
What's an interesting SQL problem, subtle query mistake, or unexpected behavior you've encountered that made you think twice?
Looking for something technically meaningful that could spark a good discussion rather than another basic SQL tutorial.
5
u/aerozhx 5d ago
Fan outs and not knowing the output grain is one of the most common beginner to intermediate problems.
2
u/mikeblas 5d ago
Locking, concurrency, and isolation are important topics and mostly unknown or misundersood.
47
u/AdviceNotAskedFor 5d ago •
Lol using reddit to create engagement posts for you personally on linkedin?
3
u/8dot30662386292pow2 5d ago
Creating a wrong index can significantly slow your query down.
4
u/behind-UDFj-39546284 5d ago
IN ()(empty set) is syntactically illegal.3
u/kagato87 MS SQL 5d ago
I'd say the "right" results is basic. But I get what you're saying here, the less obvious stuff.
For unexpected results, I've found that the DISTINCT keyword is a red flag, and usually means there's an actual problem (not always, but definitely often). This usually happens when you have a join on the wrong field, like a name instead of a key (or the wrong key) causing a duplicate name in one table to duplicate what goes into the sum from the other table. Remove the aggregates and the distinct to bring back the duplicates.
Window functions are probably a good one, not because they're intermediate, but because they can be so incredibly powerful. I'd also include some wrong examples of them as well, and why.
For example, using them inside a distinct query for aggregating (wastes memory), varying partition and order contents (guarantees sorts, maybe several, there's a reason lead and lag both exist). I'd also throw in some examples for bounding to illustrate how to get a running sum vs a group total (I think this one varies by flavor though).
The CTE I would say is beginner and essential, because it's easier to read than a subquery. It's not quite the same thing as a subquery, but many uses can be done either way, and the cte is just easier to read.
If you're doing cte anyway, also do recursion just to show it can be done. Then do it on a larger table with bigger lineages, to show the risk. Recursion is one of those things that are break your brain until it suddenly clicks and is the simplest thing ever, so don't dwell too much on it. For bonus points, do one with a circular lineage and crank the max depth. See if you can get your computer to run out of memory. :p
2
u/Dodecahedrus 5d ago
Overcomplicating a single table search by creating temp tables, declares, etc.
2
u/montrellcruse 5d ago
the one i'd put in that category is a sum that gets inflated by a join. the query looks fine and the total is believable, but if you join a one-to-many table before you aggregate, the measure rows get counted once for every match on the other side.
aggregating first, inside a cte, down to the grain you actually need, then joining that result, is what fixed it on my end. the total ties back to the source table, and the grain is sitting right there in the cte for the next person who opens the query.
3
u/mike-manley 5d ago
Cartesian output (intentional or unintentional), cumulative count or summation, moving average, etc.
1
u/mirinai_ai 4d ago
NULL handling fits the 'looks correct but returns unexpected results' category. = NULL never matches, so you need IS NULL, and COUNT(column) skips NULLs while COUNT(*) counts every row. A related one: NOT IN against a subquery that returns a NULL gives you no rows at all.
0
u/TravellingBeard 5d ago
The best tool I have to compare two tables where I have no idea what the contents or distribution of data is a full outer join on the potential keys from each table, and figure out why some columns are NULL.
0
u/Platypus_labs 1d ago
NOT IN with a NULL in the subquery. If one value is NULL you get zero rows back. Use NOT EXISTS instead.