r/learnSQL • • 4d ago

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.

21 Upvotes

12 comments sorted by

9

u/moklyhq 4d ago

One subtle bug worth discussing: a LEFT JOIN can quietly turn into an inner join when a condition on the right-hand table is placed in the WHERE clause.

SELECT customers.id, orders.id FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.status = 'paid';

Customers with no orders disappear because orders.status is NULL for them, so the WHERE condition filters them out. Moving the condition into the ON clause preserves those customers:

LEFT JOIN orders ON orders.customer_id = customers.id AND orders.status = 'paid'

Have you run into another query that looked right but changed behavior because of where a condition was placed?

1

u/BobDogGo 4d ago

i see this from professionals: writing a left join but putting an equality condition in the where clause from the right table.

1

u/AnAcceptableUserName 4d ago

Non-deterministic output in UPDATEs using FROM syntax. More broadly, ways non-deterministim manifests in SQL

Pros & cons of UDF calls vs inlining. Few high level talking points: drift, RBAR, scalar inlining

1

u/Zealousideal-Post865 4d ago

Impala&hive vs spark nulls in ordering

1

u/markinatlanta 4d ago

A query getting faster and exposing an application bug is a good one from experiece.

Did a post where we tuned a SQL Server procedure from roughly 40 seconds to under a second. Same rows, different order and there was no ORDER BY; the old plan had simply returned them consistently enough that the order looked dependable.

Before deploying, we checked whether the application relied on that sequence. The interesting discussion is how you test a performance change for assumptions that were never actually guaranteed.

1

u/waremi 4d ago

NULL, it's always null will me.

For example if your Payments table has adjustment records where CustomerId is NULL and one of those is in the past 30 days the following query will never return any records. It will look like every single Customer has made a payment in the past month.

SELECT [Name] 
FROM Customer
WHERE CustomerId NOT IN 
           (SELECT CustomerId 
            FROM Payments 
            WHERE PaymentDate>GETDATE()-30)        

The correct query is:

SELECT [Name] 
FROM Customer
WHERE CustomerId NOT IN 
           (SELECT CustomerId 
            FROM Payments 
            WHERE PaymentDate>GETDATE()-30 
              AND CustomerId IS NOT NULL)

1

u/Difficult_Paint3162 4d ago

Feel free to visit my website: Learning SQL | Real People. Real Problems. Practical SQL. No cost or sign-up required, but focuses on understanding business questions with real examples and using SQL to get the answers. Most AI tools can write good SQL, but knowing the right questions to ask is just as important

1

u/acidstained_ 3d ago

How to identify and correct duplicate data issues when creating JOINs.

1

u/rahul03rk 3d ago

A few that have bitten me in real work, all of which run without errors:

  1. A LEFT JOIN that silently becomes an INNER JOIN.
    SELECT c.name, o.total FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'paid'
    The WHERE filter on the right-hand table throws away every customer with no orders (their o.status is NULL). Move the condition into the ON clause if you want to keep them.

  2. NOT IN with a NULL.
    WHERE id NOT IN (SELECT manager_id FROM employees)
    If a single manager_id is NULL, this returns zero rows. Not fewer, zero. NOT EXISTS doesn't have this problem.

  3. LAST_VALUE that returns the current row.
    LAST_VALUE(price) OVER (PARTITION BY product ORDER BY date)
    Looks like "latest price per product", but the default frame ends at the current row, so you just get each row's own price. You need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or use FIRST_VALUE with ORDER BY date DESC.

  4. BETWEEN on timestamps.
    WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'
    Misses almost all of 31 January, because '2026-01-31' means midnight. Use created_at >= '2026-01-01' AND created_at < '2026-02-01'.

  5. Average of averages.
    Averaging daily averages to get a monthly average weights a quiet day the same as a busy one. The result can be noticeably off from AVG over the raw rows, and nobody notices because it's still a plausible number.

  6. ORDER BY on a non-unique column with LIMIT/OFFSET.
    Paginating by ORDER BY created_at when timestamps tie means rows can appear on two pages or on none, and it changes between runs. Add a unique tiebreaker like id.

What they have in common: none of them error, and the output looks reasonable. That's what makes them good discussion material. Most people have shipped at least one of them.

1

u/Optimal_Law_4254 9h ago

Recursively selecting a list of parts and counts from a bill of materials. It’s not necessarily just parts. You may have any number of assemblies which can also contain sub assemblies and so on. Getting down to the parts level can get complicated.

1

u/Then-Chef-623 4d ago

Holy fuck how is this kind of post allowed? Please link your LinkedIn post once you've harvested the information you need for it. Jesus christ.