New blog ✍️
I share practical lessons on:
Data grain mistakes (twins &amp; deduplication)
CASE statement priority
Collation issues in joins
Writing decision-ready queries
👉 Read here: [[<a href="https://medium.com/@syedmuhammad1422/from-ctes-to-collation-hard-lessons-from-a-real-sql-query-10a00cc91d1d" target="_blank" rel="noopener nofollow">https://medium.com/@syedmuhammad1422/from-ctes-to-collation-hard-lessons-from-a-real-sql-query-10a00cc91d1d</a>]
Would love feedback from the Database Daily squad 👋

Smiley Face

Your go-to source for the latest in database technology, trends, and tips. Covering Postgres, MySQL, Kafka, Data Warehouses, and more. 

Database Daily

A blog post shares practical SQL lessons learned from a real production query, covering data grain mistakes like duplicate handling, CASE statement priority ordering, collation issues that affect joins, and techniques for writing queries that produce decision-ready results.

A real production SQL query showed me why working SQL isn’t always correct.

Pretty decent write-up. It would be nice if you gave us the story context behind your examples first. The entire premise of your post is that SQL should be written safely to actually deliver the answers the business is looking for, but you jump right into an example about a woman and twins with no context about the … what the business is looking for or even what kinda data the schema holds. (You did give two example industries but didn’t tell us what we were looking at after 😄 )
Examples without context fall into the exact trap you’re arguing against.
Here are my thoughts on the rest–
I would have to disagree with your phrase: “These are not textbook concepts;”. Many of the things you describe are actual textbook-level ideas that you should be taught in any introduction SQL class
<ol>
<li>Use CTE to have named data sets</li>
<li><code>row_number()</code> – make sure your query is deterministic and you get back actually what you’re looking for</li>
<li><code>CASE</code> conditions – evaluated top to bottom, first met condition is an exit.</li>
<li><code>SELECT *</code> is generally frowned upon as an anti-pattern.</li>
</ol>
I loved your take on collation and this was the best suggestion in your post, IMO. Relying on bad joins can cause so many issues – data, performance… I’d follow up with a &quot;if you can <code>ALTER TABLE</code> to fix the source columns… that might be best 🙃
I have some concerns about your love for <code>LEFT JOIN</code> , haha. What happens if you get back a <code>NULL</code> ? does it mean missing data or the ‘reality’ of what happened is … null? <code>INNER JOIN</code> represents a mandatory relationship and there is a lot of value for using this, but, to your point, it is very situation specific. You should also mention that <code>LEFT JOIN</code> is generally a more expensive operation than an <code>INNER JOIN</code>, but it may in fact be the correct join to use.
You should use the join that meets your business need. Knowing the data tradeoffs between them is essential – this is one of the reasons why we use the term ‘analyst’ in the job market – making those key decisions is what separates a good or bad dba from a great one.
Ultimately, I think this was a good post with some good reminders, but I think you could write an entire one on the benefits of not blindly trusting your queries and that would be amazing.