SQL: Availability of tables for joins within the FROM

Thanks开发者_开发问答 for reading. Not really looking for a solution here, since there are a few ways around it. Rather, I'm looking for an explanation of why.

This has the same result for both SQL Server and MySQL, so I wouldn't hesitate to guess that others would produce the same result.

Tables used:

CREATE TABLE dbo.galleon_rights (id NVARCHAR(35) NOT NULL PRIMARY KEY,right VARCHAR(255) NOT NULL);
CREATE TABLE dbo.galleon_groups (id NVARCHAR(35) NOT NULL PRIMARY KEY,group NVARCHAR(50) NOT NULL);
CREATE TABLE dbo.galleon_conferences (id NVARCHAR(35) NOT NULL PRIMARY KEY,name VARCHAR(255) NOT NULL);

The following query works:

SELECT g.id,c.id,r.id
FROM dbo.galleon_rights r,dbo.galleon_groups g
INNER JOIN galleon_conferences c ON c.name=g.[group]
WHERE g.[group] NOT IN ('Test1','forumsmember') AND r.[right]!='CanView';

This query does not work, and produces an error that the g.[group] column cannot be found. The only difference between the two queries is that the order of the tables in the FROM has been switched, and the table used on the INNER JOIN is now at the beginning.

SELECT g.id,c.id,r.id
FROM dbo.galleon_groups g,dbo.galleon_rights r
INNER JOIN galleon_conferences c ON c.name=g.[group]
WHERE g.[group] NOT IN ('Test1','forumsmember') AND r.[right]!='CanView';

If you do an explicit CROSS JOIN with either table in the FROM, it works. If you do a LEFT JOIN on nothing (1=1), it works. What I would like to know is why it works when you reverse the order of the tables in the list but does not work when the table you're joining to is the first in the list.

Any ideas?

It's because of the order how tables are joined. Let's add the parenthesis to the examples you provided:

Example 1 (works):

dbo.galleon_rights r,
(
    dbo.galleon_groups g
    INNER JOIN galleon_conferences c ON c.name=g.[group]
)

Example 2 (doesn't work):

dbo.galleon_groups g,
(
    dbo.galleon_rights r
    INNER JOIN galleon_conferences c ON c.name=g.[group]
)

Example 3 (works):

(
    dbo.galleon_groups g    
    CROSS JOIN dbo.galleon_rights r
)
INNER JOIN galleon_conferences c ON c.name=g.[group]

It's attempting to do the INNER JOIN on the table immediately to the left of the INNER JOIN clause. I believe the other table is added after all of the join statements.

I think it's because SQL order of precedence in performing joins is giving you this:

First One:

  From dbo.galleon_rights r
      Cross Join -- It's cross join since there is no predicate to limit it    
             (dbo.galleon_groups g Join galleon_conferences c 
                     ON c.name=g.[group] )
  Where ...

and for the second One:

  From dbo.galleon_groups g 
      Cross Join 
          (dbo.galleon_rights r Join galleon_conferences c 
                      ON c.name=g.[group] )
  Where ...

If you apply some parentheses to control this you should be able to duplicate the behavior...

Case 1:

 SELECT g.id,c.id,r.id  
 FROM (dbo.galleon_rights r,dbo.galleon_groups g)
      INNER JOIN galleon_conferences c 
           ON c.name=g.[group]  
 WHERE g.[group] NOT IN ('Test1','forumsmember') 
        AND r.[right]!='CanView';  

or Case 2:

 SELECT g.id,c.id,r.id  
 FROM dbo.galleon_rights r, 
     (dbo.galleon_groups g
        INNER JOIN galleon_conferences c 
             ON c.name=g.[group] )
 WHERE g.[group] NOT IN ('Test1','forumsmember') 
        AND r.[right]!='CanView';  

It gets very confusing if you mix old and new style joins.

from tbl1, tbl2
inner join tbl3 on tbl1.id = tbl3.id

Now it looks like the , operator has a lower precedence than the join operator, so this gets executed as:

from tbl2
inner join tbl3 on tbl1.id = tbl3.id
cross join tbl1

Which gives an error because tbl1 has not been joined at that point. If you swap tbl1 and tbl2, the query does work:

from tbl2, tbl1
inner join tbl3 on tbl1.id = tbl3.id

When you cross join (implied by the comma), there is no join condition so g.group is not available to be JOINed. As expected.

I'll rewrite the two queries into what you are really doing:

SELECT g.id,c.id,r.id
FROM 
    dbo.galleon_groups g
    CROSS JOIN
    ( --g isn't in scope
    dbo.galleon_rights r
    INNER JOIN
    galleon_conferences c ON c.name=g.[group]
    )
WHERE 
    g.[group] NOT IN ('Test1','forumsmember') AND r.[right]!='CanView';

SELECT g.id,c.id,r.id
FROM 
    dbo.galleon_rights r
    CROSS JOIN
    (
    dbo.galleon_groups g
    INNER JOIN
    galleon_conferences c ON c.name=g.[group]
    )
WHERE 
    g.[group] NOT IN ('Test1','forumsmember') AND r.[right]!='CanView';

Notes:

• what is the relationship between galleon_rights and galleon_conferences
• why not dbo.galleon_conference?
• I'd expect different results because they are different queries