4 ms·

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 t

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.

roganp 5y ago
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.
hyperman1 5y ago
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.
- failedengineer 5y ago
  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.
  - hyperman1 5y ago
    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.
denton-scratch 5y ago
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?

roganp 5y ago

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.

hyperman1 5y ago

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.

failedengineer 5y ago
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.
- hyperman1 5y ago
  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.

denton-scratch 5y ago

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?