Claude Code Convinced Me To Simplify My SQL JOINs

For the last 20 years, I've had very strong feelings about how you should structure your SQL JOIN clauses. In my mind, a SQL statement is meant to be read from top to bottom. Which historically meant that each FROM, INNER JOIN, and ON clause described the "path" that the query should take when looking for the desired records. In essence, each ON clause described both the relationship between the two tables and the filtering that should be performed as part of that relationship.

To be clear, this had nothing to do with how SQL works under the hood. For everything that I do, the query optimizer is already doing the same thing. My SQL structure was never about fixing the query performance — it was about telling a human story.

As an illustration, consider when the missus sends me to the supermarket to pick up "Quaker Oatmeal" (num-nums!). As I'm walking through the store, I'm doing many things simultaneously:

1. Looking for the correct isle.
2. Looking for the oatmeal section.
3. Selecting the Quaker brand.

To transpose this human perspective onto a SQL statement, it would look like this:

FROM
	isle i
INNER JOIN
	product p
ON
	(
			i.number = '3' -- The isle I'm in.
		AND
			p.isleID = i.id -- The products in THIS isle.
		AND
			p.type = 'Oatmeal' -- Am I looking at the right product?
		AND
			p.brand = 'Quaker' -- Am I looking at the right brand?
	)

This is how I think about the world. And databases are all about mapping the world onto a digital persistence layer. So it always made sense for my SQL structure to mimic the way my brain works.

As a human man with a human man brain, I do not:

1. Pick up every product in the store.
2. Put down every product that isn't:
   • The correct isle.
   • The correct type.
   • The correct brand.

And yet, this mentality is transposed onto how most SQL statements are structured:

FROM
	isle i
INNER JOIN
	product p
ON
	p.isleID = i.id -- Pick UP EVERY PRODUCT in the store.

-- ... then once I have EVERY SINGLE PRODUCT in my basket...

WHERE
	i.number = '3' -- Put DOWN products in other isles.
AND
	p.type = 'Oatmeal' -- Put DOWN products of other types.
AND
	p.brand = 'Quaker' -- Put DOWN products of other brands.

Again, to be clear, these two approaches are functionally equivalent. The SQL database is doing this work for you behind the scenes. Both SQL statements do the same work, use the same optimizations, leverage the same indices and yield the same result.

The difference is only in how the SQL statement itself "tells the story". And Claude Code finally convinced me to get on board with using the latter approach.

How Claude Code Changed My Mind

At work, we do a lot of "vibe coding". But in my personal life, Claude Code is mostly here in an advisory capacity. I do my deep thinking, I execute the work, and then (sometimes) I ask Claude if there are ways for me to improve my technique.

Most of the time, Claude Code doesn't have many suggestions. Much of the time, the suggestions that it does have are not very persuasive. But one suggestion that it refused to give up on was simplifying my SQL statement structuring.

For the SQL joins, it made several compelling arguments:

1. Most people don't actually think about the SQL.

2. Most people don't actually read the SQL from top to bottom.

3. The SQL clauses provide a separation of concerns:

   • The JOIN describes how the tables are related.
   • The WHERE describes how the rows are filtered.

4. Isolating filtering in the WHERE clause makes SQL easier to debug because it simplifies the ability to remove all filtering and then incrementally re-add filtering.

5. LEFT OUTER JOIN relationships often have to move filtering into the WHERE clause anyway (or subtle bugs can arise).

Arguments 1 and 2 are mostly about how much people are susceptible to wearing blinkers; and about how much they rely on momentum and muscle memory to get the work done without thinking too deeply. For my personal work, this isn't that relevant. However, SQL is something that I use both personally and professionally. As such, there is absolutely value in keeping techniques consistent if it means working with the grain of how so many other people think.

Arguments 3 and 4, though, were really what sold me. It forced me to stop and think about debugging complex SQL queries in the past; and, about the friction of going to a number of different ON clauses and then commenting-out just part of the condition.

And as much as I do still believe that a compound ON statement better aligns with my "human thinking", I'm now convinced that separating "joining" and "filtering" into two distinct areas of the SQL statement has a superior value.

And even as I was still wavering on the balance of it, the 5th argument was what removed any remaining doubt. As much as I loved having all my filtering collocated in one place, using a LEFT OUTER JOIN often required moving some filtering to the WHERE clause such that it (the filtering) was applied to the resultant rows and not to the table relationship.

By treating the WHERE clause as the one place that filtering would be applied, it meant that both INNER JOIN and LEFT OUTER JOIN relationships would be treated mechanically the same. And dang it if I don't love me some uniformity!

I'm Not Crazy, I Just Need To Be Convinced

I'm not crazy; I'm just never convinced by the "this is how we've always done it" argument. I need you to point to the doll and show me exactly how your decisions helped you. I'm a reasonable person and I need reasonable arguments. And that is exactly what Claude Code did — it gave me reasonable arguments that were stronger than my reasonable arguments.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/4892