Left or Right Join? Should I Care?

In a SQL query – and we’ll call this at the physical level, because it depends on the actual implementation of a generated query – LEFT or RIGHT for a join is determined by the actual position of the tables involved in the textual query definition. In “a LEFT JOIN b”, a is at the left of b in the text, and it makes sense to use the term “left”.

LEFT/RIGHT in Visual Data Model

But what about our visual conceptual level, in the Query Xtractor builder? We use below an exclusive join, which transparently translates into an outer join. Can we use a similar approach if the shape A is at the left of B? What if we switch them, should we translate this now into a right join? But what about when we cannot tell at all if either of them is at the left or right?

a LEFT JOIN b = b RIGHT JOIN a

Remark a LEFT JOIN b is always totally equivalent with b RIGHT JOIN a! For this reason alone, the end user should not actually care if such an outer join translates, in the generated SQL query, into a LEFT join or its equivalent RIGHT join syntax. This is a SQL implementation detail.

What our Query Xtractor end user should understand is what the small little circle actually means. It’s rather about all included or all excluded, from table a or b.

LEFT JOIN Optimization

There are two reasons we implemented a LEFT JOIN optimization in our engine. It is by default ON and we recommend you keep it this way. What we try to do is use as many LEFT JOIN notations in a query, with fewest RIGHT JOINs.

First, SQLite doesn’t support RIGHT JOIN. So all eventual RIGHT JOIN occurrences must be transparently translated into their equivalent LEFT JOIN notation. There are situations however where not all RIGHT JOINs from a query can be successfully translated into equivalent LEFT JOINs, and so the SQLite query compilation will fail.

Second, combining both LEFT and RIGHT joins in a SQL query may make the query harder to understand. Trying to express as much as we can in just LEFT JOIN occurrences suggest a fluent notation, it’s like “get as much from current element, and next element in chain could be missing, it’s fine”.

The OUTER Term

For historical reasons, we continue to use optionally in SQL the OUTER keyword. But this is redundant and may create confusion. There is no OUTER keyword used in our generated SQL queries. We may still refer to LEFT/JOIN/FULL joins as “OUTER” joins, but we never generate a query with “a LEFT OUTER JOIN b” or such.