Mixing and matching implicit and explicit JOINs

Back in the caveman days, there was only one way to JOIN tables in a SQL query. Now, we refer to this as "table list", "theta" or simply "implicit" JOIN syntax:

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

There are a few problems with this syntax. For one, it makes it easy to forgot your WHERE clauses, which could lead to an exponentially larger result set that you were expecting. This is due to the default JOIN behavior resulting a Cartesian product. Also, it does not make it easy to see which filter clauses go with which tables. Lastly, it does not allow OUTER JOINs.

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

Once these deficiencies were realized, they were corrected in the SQL-92 standard. That's why the current best practice is to use explicit JOIN syntax:

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

However, what would happen if you tried to mix and match implicit and explicit JOINs? I recently found out, thanks to an open bug in Hibernate. Say your witless Hibernate layer generates the following SQL:

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

I can't tell for certain whether this is technically invalid SQL. What I can tell you is that it blows up spectacularly in Microsoft SQL (2000/2008) and MySQL.

The column prefix 'e1' does not match with a table name or alias name used in the query. -MS SQL 2000

Unknown column 'e1.managerEmployeeID' in 'on clause'. -MySQL

Sorry, no real solution at the moment. I'm off to write a one-off JDBC implementation for this data access object... If you want to experience the pain yourself, you can setup these toy tables with the following SQL:

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

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

Chase Seibert