Why is Mixing Implicit and Explicit JOINs in SQL Invalid Syntax?

Mixed implicit and explicit JOIN

Although this is an open question, mixing implicit and explicit JOINs in SQL results in invalid syntax. While explicit and implicit JOINs can work individually, combining them can lead to errors.

Consider the following example pattern:

CREATE TABLE Employee (
    employeeID INT,
    name VARCHAR(255),
    managerEmployeeID INT   
)

and sample data:

INSERT INTO Employee (employeeID, name) VALUES (1, 'Gary')
INSERT INTO Employee (employeeID, name, managerEmployeeID) VALUES (2, 'Bob', 1)

The following explicit JOIN query retrieves results as expected:

SELECT e1.name,
       e2.name,
       e1Manager.name
  FROM Employee e1
 CROSS JOIN Employee e2
 INNER JOIN Employee e1Manager
    ON e1.managerEmployeeID = e1Manager.employeeID

Similarly, this implicit JOIN query also works:

SELECT e1.name,
       e2.name,
       e1Manager.name
  FROM Employee e1,
       Employee e2,
       Employee e1Manager
 WHERE e1.managerEmployeeID = e1Manager.employeeID

Invalid SQL

However, when trying to mix implicit and explicit JOINs:

SELECT e1.name, 
       e2.name, 
       e1Manager.name
  FROM Employee e1,
       Employee e2
 INNER JOIN Employee e1Manager 
    ON e1.managerEmployeeID = e1Manager.employeeID

Errors will occur in both MSSQL 2000/2008 and MySQL. In MS2000 the error is:

<code>列前缀'e1'与查询中使用的表名或别名不匹配。</code>

In MySQL, the error is:

<code>'on'子句中未知列'e1.managerEmployeeID'。</code>

Why is this syntax invalid?

The SQL standard stipulates that the JOIN keyword has a higher priority than commas. However, table aliases can only be used after the corresponding table has been evaluated in the FROM clause.

In the mixed JOIN query, the JOIN...ON expression refers to e1, but e1 has not yet been evaluated because the comma-separated list of tables has not been processed yet.

Extra: Force Hibernate to use explicit JOIN

Unfortunately, my research into Hibernate's HQL documentation didn't turn up any information on how to force the use of explicit JOIN.

The above is the detailed content of Why is Mixing Implicit and Explicit JOINs in SQL Invalid Syntax?. For more information, please follow other related articles on the PHP Chinese website!