How is resolved tables visibility in sql join

Question

I have problems understanding the correct syntax of sql using explicit joins. For example in the first snippet, in the second join "ec" is visible and the restriction works. But in the second snippet "op" cannot be resolved.

First snippet:

select im.* 
  from gestion_corte gc 
  join evento_corte ec
       on ec.id_corte = gc.id_corte
  join op_corte op
       on op.id_corte = gc.id_corte
       and op.fecha_evento = ec.fecha_evento        
  join img_corte im        
       on op.id_corte = im.id_corte                     
 where ec.fecha_evento > '01092012'

Second snippet, "op" cannot be resolved in first join:

select im.* 
  from gestion_corte gc 
  join evento_corte ec
       on ec.id_corte = gc.id_corte
       and op.fecha_evento = ec.fecha_evento -- This condition has moved
  join op_corte op
       on op.id_corte = gc.id_corte        
  join img_corte im        
       on op.id_corte = im.id_corte                     
 where ec.fecha_evento > '01092012'

In consequence, the visibility is resolved processing from top to bottom? are any other important thing to have in consideration?

You cannot reference a table in a join condition unless it has already been declared in the table list. [Here](http://stackoverflow.com/a/5009727/880904) is a nice explanation of when join order will or won't matter. — Tim Lehner, Nov 16 '12 at 17:11
I think they have explained your diffulty with the given snippets pretty well. One other important thing you should be aware of in joining concerns left joins: http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN — HLGEM, Nov 16 '12 at 20:45

Gordon Linoff · Answer 1 · 2012-11-16T17:30:00.713

2

In the SQL join syntax, an alias is not known until it is defined. So, you can do:

from A a join
     B b
     on a.val = b.val join
     C c
     on a.val = c.val

However, you cannot do:

 from A a join
     B b
     on a.val = b.val and
        a.id = c.id join
     C c
     on a.val = c.val

The SQL engine simply does not know what "c" is, because "c" has not been seen yet in the from statement.

You second query is easy to fix by moving the condition, as you discovered.

edited Nov 16 '12 at 17:30

answered Nov 16 '12 at 17:09

Gordon Linoff

1,242,037
58
646
786

It's not really a matter of aliasing it just makes no conceptual sense for a join between table `A` and `B` to have any condition referencing `C`. A join is between two virtual tables and `C` isn't in either of them. – Martin Smith Nov 16 '12 at 17:16
@MartinSmith, while I agree that it doesn't make sense, I've never been stopped by the query analyzer from a silly copy and paste mistake such as: `from a join b on a.x = b.x join c on a.x = b.x` – Tim Lehner Nov 16 '12 at 17:33
@TimLehner - That's because the output of the first join is a virtual table with `a x b`. You are joining that virtual table to `c` so it is valid to reference `a`,`b`, or `c` in the join condition. The OP would get their original error if they didn't use table aliases at all. – Martin Smith Nov 16 '12 at 17:34

Martin Smith · Answer 2 · 2012-11-17T10:42:06.347

A join is between two virtual tables.

The join predicate can only refer to columns from these two virtual tables (though each virtual table may consist of columns from multiple base tables).

It is not necessarily the case that the order of valid usage is top down as the logical join order is determined by the order of the ON clauses. So for example in the following query (SQL Fiddle) the virtual table (a join b) is joined onto the virtual table (c join d join e)

SELECT *
FROM   a
       JOIN b
         ON a.id = b.id /*Only a and b in scope here*/
       JOIN c
            JOIN d
              ON c.id = d.id /*Only c and d in scope here*/
            JOIN e
              ON e.id = d.id /*Only c, d, e in scope here*/
         ON d.id = a.id  /*a,b,c,d,e all in scope*/

This query (SQL Fiddle) would give an error as a is not in scope there.

SELECT *
FROM   a
       JOIN b
         ON a.id = b.id
       JOIN c
            JOIN d
              ON c.id = d.id
            JOIN e
              ON e.id = a.id -- Can't reference a here
         ON d.id = a.id

pkuderov · Answer 3 · 2012-11-16T17:27:51.290

I don't get your second question, so I have only answer to first one.
Your joins can be imagined this way:

"result" = select "some columns" from "any source"

"any source" can be a table or a subquery (i.e. another "result") or "any source" join "any source":
"any source" = table | "result" | "any source" join "any source"

So if you want a "result" you should determine "any source" first then "some columns" (let's don't focus on it). Let's go to the "any source" definition to determine it. Table is determined, "result" recursively leads to prevprevious sentence and join construction has a rule - to determine it firstly get left "any source" then right.
join is a left associative operation and isn't a commutative operation generally (inner join and cross join are, but not the left join or right join).
So, look at your 2nd snippet. This part gestion_corte join evento_corte hasn't been calculated because of op_corte hasn't been determined yet

How is resolved tables visibility in sql join

3 Answers3