CODEWITHSUNDEEP
sqlpostgresqljoinouter-join
vektor
vektor

Reputation: 2916

Multiple LEFT JOINs - what is the "left" table?

I've been using this for years, so it is high time to understand it fully. Suppose a query like this:

SELECT 
  *
FROM a
LEFT JOIN b ON foo...
LEFT JOIN c ON bar...

The documentation tells us that

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression

LEFT OUTER JOIN

First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1.

The question is simple: what is T1 in this case? Is it a? Or is it a LEFT JOIN b ON foo? (or, is it the same?)

Upvotes: 17

Views: 12026

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269723

A FROM clause parses the conditions from left to right (unless overridden by parentheses). So:

FROM a 
LEFT JOIN b
     ON foo... 
LEFT JOIN c
     ON bar...

is parsed as:

FROM (
        a 
        LEFT JOIN b
           ON foo...
     ) 
LEFT JOIN c
     ON bar...

This is explained in the documentation under the join-type section of the FROM clause:

Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right. In any case JOIN binds more tightly than the commas separating FROM-list items.

As a consequence, a series of LEFT JOINs keeps all records in the first mentioned table. This is a convenience.

Note that the parsing of the FROM clause is the same regardless of the join type.

Upvotes: 11

Gleb Kogtev
Gleb Kogtev

Reputation: 111

Here is multiple join operation. SQL is a language where you describe the results to get, not how to get them. The optimizer will decide which join to do first, depending on what it thinks will be most efficient. You can read here some information
https://community.oracle.com/thread/2428634?tstart=0
I think, it works the same for PostgreSQL

Upvotes: 0

Walter_Ritzel
Walter_Ritzel

Reputation: 1397

It could be both, depending on how you are joining the data (the foo and bars on your example).

For example, if in your example, you want to join a with b and a with c, T1 will be a.

But, if your intention is to join a with b and the result of that with c, then T1 will be a LEFT JOIN b ON foo.

In the last case, would be an improvement on readability if you write like this:

(a LEFT JOIN b ON foo) LEFT JOIN c ON bar

Upvotes: 0

Related Questions