[beefield]

Well, I consider myself somewhat fluent with SQL, and for some reason left joins are the ones that occasionally get me really confused - so much so that I actively try to avoid them. Trouble is not in the vanilla cases, but when you start throwing multiple tables and multiple where clauses in the same query, then there is something about left joins and nulls that is really unintuitive to my brain. Maybe I should spend some time and study the left join more...

[kevincox]

I think left join is a bad name. Probably something like OPTIONAL JOIN or TRY JOIN would be more obvious. Of course the problem is then what do you call a right join? REVERSE OPTIONAL JOIN? But that is getting pretty confusing now. But maybe worth it because in my experience left is far more common.

[samatman]

It's best to pretend that RIGHT JOIN doesn't exist, imnsho.

For one thing, in SQLite, it doesn't. Which is a weak argument for not using it on supported systems. The other weak argument is that a RIGHT JOIN is just the b, a version of a LEFT JOIN a, b.

When you add them up it's an extra concept, SQL execution flow is already somewhat unintuitive, and a policy of using one of the two ways of saying "everything from a and matches from b" makes for a more consistent codebase.

I would hope a blue-sky relational query language wouldn't support two syntaxes for an operation which is non-commutative, when order is important it can and should be indicted by order.

[debugnik]

> For one thing, in SQLite, it doesn't.

It does now, since the latest release 3.39, along with full join.

[samatman]

That's great news!

It'll be a long time before one can use it in portable SQLite queries, for those cases where that matters. I'll continue to eschew the right join for the clarity of only thinking about that relation in one way, but we statically link SQLite for several good reasons, including being able to use new features as they arrive.

Full join is certainly a welcome addition.

[motoboi]

It's actually pretty obvious name. You just need to visualize a Venn diagram and left, right and inner become obvious.

[mmsimanga]

You are right about the LEFT join coupled with a filter in the WHERE clause sometimes makes the join a normal INNER join. Just when this is the case is difficult for me to work out. This is often a trick question in some SQL assessments I have seen. I say this as someone with over 20 years of SQL almost on a daily basis. I avoid such scenarios by using CTEs.