5 ms·

Had been writing SQLs for decades. In the beginning tried all kinds of joins, but every one of those had some unwanted side effect. Especially where there were

Had been writing SQLs for decades. In the beginning tried all kinds of joins, but every one of those had some unwanted side effect. Especially where there were un-expected nulls. Figured out LEFT JOIN eliminated all of those and produced what I want. Had been using LEFT JOIN since. I ever remember only one time when I didn't use left join, because that case needed a full join.

eurasiantiger 5y ago
Same experience here. LEFT JOIN does exactly what I want every time, and keeping to it makes complex queries easier to work with. Other join types simply aren’t necessary in the majority of web/CRUD operations even with complex relationships. If they feel that way, the query is likely somehow ass-backwards to begin with.
dotancohen 5y ago
I also remember only a single "can't-use-a-left-join" problem. Left joins express "Give me these things, and these other sub-things related to them". Object oriented programming lends itself towards the thing-with-subthings (and collections of things-with-subthings) so I feel that the left join is the default choice for most applications which are structured with the classic OOP approach. I wonder if full joins are more common in other programming paradigms, such as whatever those crazy lisp folks talk about.
caaqil 5y ago
I think we found Jared.
paul_f 5y ago
Count me in. Left joins only. It works, is fast, and I only need to remember one type of join
lvl102 5y ago
This is so much me. I used to be an SQL junkie back in my 20s but the work I did was critical. As in if my query was wrong even due to some weird software quirk/edge case/bug, that could mean a few million dollars or even into the billions. That type of pressure leads you to write SQL (or code in general) in a very specific way. OTOH, if I wrote my SQLs in that fashion during data science interviews, these young “scientists” will tell me I am a novice.
unyttigfjelltol 5y ago
I never understood why there were so many other kinds of joins. Using only LEFT joins makes it easier to hold a mental model in mind, because you're always running left to right, superior to dependent. Occasionally I would flip to a RIGHT join for testing or adjusting and the structure immediately would be much harder to hold in mind.
matwood 5y ago
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.
goto11 5y ago
> In the beginning tried all kinds of joins, but every one of those had some unwanted side effect. This makes it sound more mysterious than it is. Joins does not have side-effects, they are just different operators. It comes down to if you want to include unmatched rows. Left join: Include unmatched rows from the first table. Right join: Include unmatched rows from the second table. Outer join: Include unmatched rows from both tables. Inner join: Don't include any unmatched rows. Left and right join is of course the same, the only difference is in which order you list the tables. Left join seem more intuitive to me when writing queries, but logically they are equivalent.
y4mi 5y ago
Isn't the inner join the only one that's redundant though, because it's the same as doing an equals check? (from a, b where a.b_id=b.b_id
premek 5y ago
I don't even know what the other joins are
failedengineer 5y ago
This makes my head and heart hurt to see so many people agreeing with this. There's a right, and wrong, time for using left joins. "all the time" is the wrong time. If you need to join on all existing records, including null records, you need the trinary equality operation. Also... there's a right join... if you're writing complex enough stuff long enough, you'll eventually hit a query where it's just easier to do the same thing you've been doing on the right side. I can't say for sure, because it's implementation dependant, but this is going to slow down your results on larger data sets as well, since you won't be excluding joins (in memory) by excluding data (during the selects from individual tables)

eurasiantiger 5y ago

Same experience here. LEFT JOIN does exactly what I want every time, and keeping to it makes complex queries easier to work with. Other join types simply aren’t necessary in the majority of web/CRUD operations even with complex relationships. If they feel that way, the query is likely somehow ass-backwards to begin with.

dotancohen 5y ago

I also remember only a single "can't-use-a-left-join" problem. Left joins express "Give me these things, and these other sub-things related to them". Object oriented programming lends itself towards the thing-with-subthings (and collections of things-with-subthings) so I feel that the left join is the default choice for most applications which are structured with the classic OOP approach. I wonder if full joins are more common in other programming paradigms, such as whatever those crazy lisp folks talk about.

caaqil 5y ago

I think we found Jared.

paul_f 5y ago

Count me in. Left joins only. It works, is fast, and I only need to remember one type of join

lvl102 5y ago

This is so much me. I used to be an SQL junkie back in my 20s but the work I did was critical. As in if my query was wrong even due to some weird software quirk/edge case/bug, that could mean a few million dollars or even into the billions. That type of pressure leads you to write SQL (or code in general) in a very specific way. OTOH, if I wrote my SQLs in that fashion during data science interviews, these young “scientists” will tell me I am a novice.

unyttigfjelltol 5y ago

I never understood why there were so many other kinds of joins. Using only LEFT joins makes it easier to hold a mental model in mind, because you're always running left to right, superior to dependent. Occasionally I would flip to a RIGHT join for testing or adjusting and the structure immediately would be much harder to hold in mind.

matwood 5y ago

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.

goto11 5y ago

> In the beginning tried all kinds of joins, but every one of those had some unwanted side effect. This makes it sound more mysterious than it is. Joins does not have side-effects, they are just different operators. It comes down to if you want to include unmatched rows. Left join: Include unmatched rows from the first table. Right join: Include unmatched rows from the second table. Outer join: Include unmatched rows from both tables. Inner join: Don't include any unmatched rows. Left and right join is of course the same, the only difference is in which order you list the tables. Left join seem more intuitive to me when writing queries, but logically they are equivalent.

y4mi 5y ago

Isn't the inner join the only one that's redundant though, because it's the same as doing an equals check? (from a, b where a.b_id=b.b_id

premek 5y ago

I don't even know what the other joins are

failedengineer 5y ago

This makes my head and heart hurt to see so many people agreeing with this. There's a right, and wrong, time for using left joins. "all the time" is the wrong time. If you need to join on all existing records, including null records, you need the trinary equality operation. Also... there's a right join... if you're writing complex enough stuff long enough, you'll eventually hit a query where it's just easier to do the same thing you've been doing on the right side. I can't say for sure, because it's implementation dependant, but this is going to slow down your results on larger data sets as well, since you won't be excluding joins (in memory) by excluding data (during the selects from individual tables)