16

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?)

vektor
  • 3,312
  • 8
  • 41
  • 71
  • 1
    It is both, depending on how you are joining the data (the foo and bars on your example. For example, if in the statement "... JOIN c ON bar..." the conditions uses tables a and b, it will be a JOIN b ON foo. – Walter_Ritzel Mar 21 '16 at 15:43
  • 2
    `(a left join b) left join c`, i.e. first `a` is the left table, then `(a left join b)` is the next left table. – jarlh Mar 21 '16 at 15:43
  • @jarlh if you write this up as an answer and provide references, I will accept it. – vektor Mar 21 '16 at 15:43
  • Consider what it would look like if your statement were all on one line (not practical for readability, but just to understand this situation). When you use `LEFT [OUTER] JOIN` you are asking it to keep *all* of the rows on the table(s) to the ***left*** of this statement (`a`), regardless of whether or not there are any rows in the next table specified (`b`) that match your boolean expression. – CactusCake Mar 21 '16 at 15:45

3 Answers3

11

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.

IMSoP
  • 89,526
  • 13
  • 117
  • 169
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I've tweaked the indenting on your examples to emphasise the parentheses a bit more. Feel free to revert with my apologies if you disagree this looks better. :) – IMSoP Mar 21 '16 at 16:06
  • @IMSoP . . . I disagree rather strongly that it works better, but I'm not going to change it. – Gordon Linoff Mar 22 '16 at 03:08
0

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

Gleb Kogtev
  • 111
  • 1
  • 3
  • 10
  • Could you provide some references? – vektor Mar 21 '16 at 15:51
  • @Gleboss . . . The semantic order (how the joins are interpreted) is not related to the execution order (how the joins are implemented). Hence, you have answered a different question. – Gordon Linoff Mar 21 '16 at 16:05
0

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
Walter_Ritzel
  • 1,387
  • 1
  • 12
  • 16