Custom Oracle SQL Table Alias ​​in One Select

Question

Does anyone know why this works with the table alias "x"?

Select x.company_name ,x.employee_name FROM company x JOIN employee x ON x.company_id = 5 

I know that JOIN with id 5 does not make sense ...

Thank you for the lesson!

Answer 1

The first two queries are below. In the join ON clause, the alias of table x refers only to the last table using this alias, so only the employee table is limited.

In SELECT and WHERE expressions, alias x refers to both tables - therefore, when column names are unique, they can be successfully referenced, but where there are identical column names, then oracle raises the value of ORA-00918: column ambiguously defined exception (how it happens in request 3 if the comment is deleted).

I can not find the documentation on this, but it looks very much like a bug.

SQL Fiddle

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' ); 

Request 1 :

 SELECT * FROM company x JOIN employee x ON x.company_id = 5 

Results :

 | COMPANY_ID | COMPANY_NAME | EMPLOYEE_NAME | |------------|--------------|---------------| | 5 | Four Company | Five Emp | | 5 | Five Company | Five Emp | | 5 | Six Company | Five Emp | 

Request 2 :

 SELECT * FROM company x CROSS JOIN (SELECT * FROM employee WHERE company_id = 5) x 

Results :

 | COMPANY_ID | COMPANY_NAME | EMPLOYEE_NAME | |------------|--------------|---------------| | 5 | Four Company | Five Emp | | 5 | Five Company | Five Emp | | 5 | Six Company | Five Emp | 

Request 3 :

 SELECT --x.company_id, x.company_name, x.employee_name FROM company x CROSS JOIN (SELECT * FROM employee WHERE company_id = 5) x 

Results :

 | COMPANY_NAME | EMPLOYEE_NAME | |--------------|---------------| | Four Company | Five Emp | | Five Company | Five Emp | | Six Company | Five Emp | 

Answer 2

I would spend money on the "Oracle bug". Of all that kept me from using the ANSI JOIN syntax the most, it was a terribly egregious and rude execution of Oracle. However, the “Inability to catch a syntax error” is pretty minor, and Oracle, as far as I can tell, has pretty much cleared their action.