Oracle SQL non-unique table alias in one Select
I'd lay money on "Oracle bug" - Of all the things that've most held me back from adopting the ANSI JOIN syntax, it's been Oracle's painfully egregious and bug-laden implementation thereof. That said, "Failure to catch a syntax error" is pretty minor, and Oracle has, as far as I can tell, largely cleaned up their act.
The first two queries below are equivalent. In the ON
clause of the join the table alias x
only refers to the last table to use that alias so only the employee
table is restricted.
In the SELECT
and WHERE
expressions the x
alias refers to both tables - so, where the column names are unique then they can be successfully referenced but where there are identical column names then oracle raises an ORA-00918: column ambiguously defined
exception (as happens in query 3 if the comment is removed).
I can't find any documentation on this but it looks very like a bug.
Oracle 11g R2 Schema Setup:
CREATE TABLE employee ( company_id NUMBER(3), employee_name VARCHAR2(20));CREATE TABLE company ( company_id NUMBER(3), company_name VARCHAR2(20));INSERT INTO employee VALUES ( 4, 'Four Emp' );INSERT INTO employee VALUES ( 5, 'Five Emp' );INSERT INTO employee VALUES ( 6, 'Six Emp' );INSERT INTO company VALUES ( 4, 'Four Company' );INSERT INTO company VALUES ( 5, 'Five Company' );INSERT INTO company VALUES ( 6, 'Six Company' );
Query 1:
SELECT *FROM company x JOIN employee x ON x.company_id = 5
| COMPANY_ID | COMPANY_NAME | EMPLOYEE_NAME ||------------|--------------|---------------|| 5 | Four Company | Five Emp || 5 | Five Company | Five Emp || 5 | Six Company | Five Emp |
Query 2:
SELECT *FROM company x CROSS JOIN (SELECT * FROM employee WHERE company_id = 5) x
| COMPANY_ID | COMPANY_NAME | EMPLOYEE_NAME ||------------|--------------|---------------|| 5 | Four Company | Five Emp || 5 | Five Company | Five Emp || 5 | Six Company | Five Emp |
Query 3:
SELECT --x.company_id, x.company_name, x.employee_nameFROM company x CROSS JOIN (SELECT * FROM employee WHERE company_id = 5) x
| COMPANY_NAME | EMPLOYEE_NAME ||--------------|---------------|| Four Company | Five Emp || Five Company | Five Emp || Six Company | Five Emp |