Hacker News Reader

Comment by phendrenad2 | Hacker News Reader

Makes sense. When you think about it, all joins are just a left join (perhaps joined with an empty table), and some in-place filtering. Inner join? That's just a left join, and then skip any row where the joined table returned nulls. Full outer join? That's just two left joins against an empty table, sorted, and you use dual iterators to step through them in lock-step.

View on HN

Right join is just left join with the clauses switched, so it makes sense to use left join consistently. Inner join is just a left join with a 'not null' condition. I prefer inner join for conciseness, but it makes sense to always chose left join for consistency. Full outer joins are rarely needed in my experience.

In any case, all the joins are just syntactic sugar over cross product. AFAIK SQL did not have the join syntax initially, you just did SELECT foo, bar WHERE foo.id = bar.foo_id

You can't implement an outer join with a cross product.

You are right, I'm wrong. You can get left, right and inner join by applying constraints to an outer join, but you cant get an outer join from a cross product.

> When you think about it, all joins are just a left join

Not really; using left joins enforces a specific table order in the query plan.

It's possible to optimally use left joins because one can either guess the optimal order (which is a bad habit, though), or can observe the query plan and emulate it.

My guess that this developer didn't trust the optimizer to do a good job at ordering, and he wanted to enforce it, but that is generally not the case with modern engines (of course, there will always be exceptions).

> that is generally not the case with modern engines (of course, there will always be exceptions)

In my experience, the exceptions are more than the straightforward queries I can write and forget, so much that SQL grammar feels like the German grammar: Here's what you use in this scenario, except [goes on to list a million exception cases most of which makes no logical sense].

This is especially true if you are doing complicated queries across products in a multi-product multi-tenant multi-team database. Writing the idiomatic query takes a few minutes, then hours of fighting against the query planner.

Going bonkers with left joins and/or CTEs usually "fixes" the "problem". I usually leave a commented-out sane version after me for the people who can do better, and not to be blamed with writing mortgage-code.

Neither German nor SQL (with any RDBMS) is my native language anyway, so I can publicly admit my struggles without feeling too embarrassed :)

Yeah and if you put everything in as left joins filtering only happens in the where clause. It becomes easier to understand why rows are missing. I've done this in the past when I wanted to make it obvious that x was getting filtered out of the result set.

You can filter on the left table by putting the conditions in the ON clause.

Exactly you can but then your query has multiple places to examine if you want to know what out you should expect. By having it all in the where clause it can read a little better.

>When you think about it, all joins are just a left join

Absolutely not. All joins can be related, or explained by using left join as a reference, but there's no such thing as a "default join", or perhaps there is, and it's up to the implementation of the database. Taking the analogy further, all joins are outer joins, just apply some filters, right? Or a left join is just an inner join, and then you include the rest of the left table. It's worthwhile pondering about this, but "all joins are just a left join" is a moot point.

I'm surprised nobody else has mentioned this yet - a LEFT JOIN can be more efficient than an INNER JOIN precisely because the database doesn't need to enforce the filter during the query plan.

This comes up for me most often with views, when a query referencing the view doesn't end up using all the columns - when a LEFT JOIN is used, the database can potentially skip querying those tables entirely, but not with an INNER JOIN.

I imagine a theoretical programmer who only used while loops, or did everything using recursion. Not reason to ever touch a for loop really. There are plenty of shortcuts that obscure what's happening under the hood. If you can do it all "closer to the metal" as it were, then why bother?

I think having a preferred iteration strategy is important. At my first job I was taught consistency consistency consistency.

The more ways you do thing the more complexity you add to the code. If you have a project with the same patterns over and over again it becomes easy to learn and easy for Juniors to contribute to.

Reply on news.ycombinator.com