What are some interesting SQL problems or query mistakes worth discussing?

[u/Due_Counter_2286]

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.

[u/aerozhx]

Fan outs and not knowing the output grain is one of the most common beginner to intermediate problems.

[u/Due_Counter_2286 → u/aerozhx]

Absolutely, understanding the expected grain before joining or aggregating data is important. Otherwise, it's easy to introduce duplicates or incorrect totals without immediately noticing.

[u/mikeblas]

Locking, concurrency, and isolation are important topics and mostly unknown or misundersood.

[u/Due_Counter_2286 → u/mikeblas]

Transaction isolation and concurrency are important topics, especially when queries behave differently under concurrent workloads. Thanks for bringing them up!

[u/AdviceNotAskedFor]

Lol using reddit to create engagement posts for you personally on linkedin?

[u/BrentOzar → u/AdviceNotAskedFor]

Exactly. “Tell me what to prompt ChatGPT for so I seem like I know what I’m talking about.” Sure, I’ll get right on that.

[u/heisoneofus → u/AdviceNotAskedFor]

Been like this since forever. OP simply admitted it because they are not very smart.

[u/8dot30662386292pow2]

Creating a wrong index can significantly slow your query down.

[u/Due_Counter_2286 → u/8dot30662386292pow2]

Definitely worth exploring further.

[u/behind-UDFj-39546284]

IN () (empty set) is syntactically illegal.

[u/techforallseasons → u/behind-UDFj-39546284]

Which is odd because while () will throw; a predicate clause of:

colA IN ( SUBQUERY ) - where subquery has zero rows does not throw

The contents of the IN () is an empty set in either state, but an empty sub-query result set is "fine"

[u/behind-UDFj-39546284 → u/techforallseasons]

Yep, this is odd as a syntax design decision. I understand that col IN () is semantically effectively merely FALSE, but I discovered this oddity by accident while working with ORMs that blindly generate, as it turns, invalid SQL statements, just because I passed empty collections to bind prepared statements, which required more code to work around that (which also requires imperative "is empty" rather than a "linear" query generation). Perhaps this makes some sense from the perspective of semantics, but this is still something I don't know the design reason for.

EDIT: SQLite accepts SELECT 1 IN () returning FALSE (0).

EDIT2: https://groups.google.com/g/mailing.database.pgsql-sql/c/8iNfREHcdzg?utm_source=chatgpt.com (Yeah, I was trying to ask ChatGPT to find the origin) Somebody in the thread points out that some defensive code may save on unnecessary database calls, by on other hand I may ask the database tell the result of SELECT 1 + 1; just because this is the query.

[u/kagato87]

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

[u/Dodecahedrus]

Overcomplicating a single table search by creating temp tables, declares, etc.

[u/montrellcruse]

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.

[u/mike-manley]

Cartesian output (intentional or unintentional), cumulative count or summation, moving average, etc.

[u/mirinai_ai]

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.

[u/TravellingBeard]

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.

[u/Platypus_labs]

NOT IN with a NULL in the subquery. If one value is NULL you get zero rows back. Use NOT EXISTS instead.