Hacker News Reader

Comment by matwood | Hacker News Reader

As someone who has been writing sql for 20 years (yikes I'm old), a left join would convey to me the author expects unmatched records in the joined table. For that reason alone I would use inner joins to convey inner join semantics, and fix whatever is 'un-expected'.

Using left joins exclusively feels like it's papering over a larger issue that should be addressed.

View on HN

I'm old too.

As far as I'm concerned, LEFT JOIN is simply shorthand for LEFT OUTER JOIN, and is semantically equivalent to a RIGHT JOIN, but with the order of the tables reversed. If what you really need is an INNER JOIN, and you use a LEFT JOIN instead, then you have a load of processing to do on the result-set.

So if this guru "Jared" is only ever using LEFT JOIN, then either he's addressing a rather constrained problem space, or he's isn't that much of a SQL guru. Maybe he designs schemas in which LEFT joins are always equivalent to INNER joins?

[Edit] I'm wrong; you can, of course, get the effect of an INNER JOIN by using a LEFT JOIN with a NOT NULL join condition. But I find the latter hard to read: there must be some reason this guy has used an outer join here, when it looks as if an inner join fits the bill?

Exactly. It's always inner joins unless there is a reason not, and then there should be logic to deal with the null results from the outer table. It's not strange to always do left outer either, it just makes reasoning about the query easier (start with the driving set, the inner/outer join the others as left joins). Mixing right and left outer always confuses me, and full outer joins are difficult to optimize, so I usually do left outer joins with unions in the rare case that I need a full outer join.

It depends on the situation.

If you're writing code, you're strategy is clearly better because it documents an expectation.

If you're dropped in a database which is unknown territory, say to debug an issue, left joins represent a conservative guess, maybe even allowing to follow up with verification querys: where right join side is null.

I'd read the different responses here as people having different roles.

It's not a conservative guess. It's objectively wrong. Inner join means "include only matching records," and if nulls are being dropped, it's because the developer doesn't know that in SQL, null != null, and they need to address the defect in the code that excludes that case.

If you're debugging something, there tends to be a defect to address in the vicinity ;-) Maybe also add there is a difference between an ad hock query, where conservative guesses are acceptable, and queries built into code, where you'd better know what you're doing.

I've done it a few times, typically in a situation where some random contractor dropped a minimal effort database design right into production. No constraints like not null of course, that would mean the application shows an error and contractor can't have that because it would imply they did something bad.

So the DB chugs along for months, accreting damaged data. Then something finally crashes bad enough to wake up the management, which instantly switches from 'everything is perfect lalalalaaa' messages into blind panic mode.

So you get thrown into the mess, sometimes without even knowing what the application is supposed to do, with the directive: Fix this, we're bleeding money. After looking around for about 5 seconds, it turns out everything is objectively wrong. You can't fix all fires at the same time, you have to put out one at a time, and try not to make it accidentally worse. That's when you use left joins as a conservative guess, at least until you've built up enough knowledge.

Reply on news.ycombinator.com