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.
Using left joins exclusively feels like it's papering over a larger issue that should be addressed.
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.
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.
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?
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.
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.
OTOH, if I wrote my SQLs in that fashion during data science interviews, these young “scientists” will tell me I am a novice.
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)