Claude Code Convinced Me To Simplify My SQL JOINs

@Chris,

I think it's how most people think, so you're in good company. I think I can't hold too many things in my head at once; so there was always something very comforting about being able to see both the relationships and the filtering right in the same place. It gave me a very tightly scoped bit of information to wrap my head around.

I'm also trying to be good / better about taking tips and pointers from Claude / AI. It's been a bit hit-and-miss, though. Sometimes it will make suggested changes. But then when I pressure-test those changes, asking for more clarity and reasoning, it will often fess-up that it's suggestions were not as strong as they were originally stated to be.

That's what made this suggestion so interesting — Claude refused to back down. It kept asserting that this was the right approach to use.

@Gary,

First of all, +1000 on Navicat! It's been my favorite SQL GUI for like 15 years. It's just a super solid product. I've even used it a bit for MongoDB... and I think they may even have a Redis GUI as well. But, I'm in the SQL biz like 99.99% of the time.

That said, it's been a while since I've worked on anything where the SQL gets too tricky. These day, most of DB reads are either ID-based look-ups for mutation or view-specific JOIN for optimization. It's the latter that only ever brings this to light. And for those, I run an EXPLAIN locally (in dev) to make sure it's not crazy; and then I kind of just "vibe it out" in production to make sure the performance feels right.

When I was in a more high-volume environment at InVision, I would often use Fusion Reactor (application performance monitoring tools) and the MySQL slow log to keep track of slow running requests. Then, when things showed up, I would address them at that time.

For the most part, the dev/prod divide worked "the same" like 99% of the time. But, there was definitely that 1% case where the query optimizer just went on some crazy acid-trip once the SQL got deployed. 😨

@Carl,

The LEFT OUTER JOIN was one of the things that finally sold me on moving more conditions to the WHERE clause. Depending on what the condition is doing / what your needs are, the LOJ can accidentally remove rows from the results.

For anyone interested in this point, consider a user table with an optional timezone table. If one were to do:

FROM
	user u
LEFT OUTER JOIN
	timezone t
ON
	(
			u.id = ?
		AND
			t.userID = u.id -- FAIL (no matching row)
	)

... if there is no corresponding t row, then I think (don't have a DB running to test this) it will end up returning all users since the condition fails and nothing is there to limit the left-hand selection.

However, if you move the u.id condition to the WHERE:

FROM
	user u
LEFT OUTER JOIN
	timezone t
ON
	t.userID = u.id -- FAIL (no matching row)
WHERE
	u.id = ?

... then you still get the u.id=? in the WHERE even though the JOIN fails. Which means you still only get one record.

So, by moving all the filter conditions into the WHERE, you basically never need to worry about this.

Caveat: I do think it matters if you want the right-hand side of the result to be NULL for certain things, though. If you want the right-hand side to be NULL, you have to move conditions into the ON to break the cross-product.

@Gary,

So far, I haven't really used AI to do anything that I couldn't already do (with the exception of one or two experiments that I've already lost interest in). What I'm hoping to use it for mostly is to allow me to take larger steps outside of my comfort zone.

For example, I've never played with full-text indexing. This site has no search. Would people use search anyway? Probably not. I actually just use site:bennadel.com {query} in Google when searching the site. But, with AI, I feel more confident that I could get full-text indexing working with a new technology (like Elastic Search or something).

@Gary,

It's a fascinating point. We're wildly off-topic at this point, but who cares 😂 I see a lot of people talking about how easy it is to just rebuild some prior art. One-shotting some existing SaaS product or converting a library from one language to another. The impressive bit there is obviously in the scope of what's being done (and the shocking degree to which it "just works").

But, in my experience, what's really hard isn't the coding — it's the talking to the client, understanding what they're asking for, trying to find the right UX to suite their needs, and then iterating with them day-after-day to arrive at the right solution.

There's no way to "one shot" what's in someone else's head!

@Gary,

Ha ha, I wasn't complaining at all! Tangential conversations are the spice of life 😂

As far as query of queries goes, I've taken to returning arrays from all of my data access methods. And then I can do the filter and map methods on those arrays. Not better or worse, just coming at it from a parallel angle that works well for me. It's so much fun to think about this stuff!

@Gary,

It is super interesting! And it's great that they were able to move past a vendor that was clearly treating them terribly. But two things do pop to mind. 1) there is something kind of "icky" about the notion of screen-shotting someone else' work and just saying "copy that". I totally get that they were between a rock-and-a-hard-place; but, the idea that our industry is now all OK just copy-pasting other people's work doesn't sit great - it's a brave new world. And 2) they now have to maintain this software going forward.

So, on the one hand, it's bonkers how good something of this tooling is getting. But on the other hand, I always think at what cost.

@Gary,

Ha ha, very true.