Why doesn't Oracle offer an “ambiguous column reference” here?

Question

I have some SQL queries here -

WITH emp AS (SELECT 1 AS empid, 'Adam' AS ename, 10 AS deptno, 'Broker' AS description FROM dual UNION ALL SELECT 2, 'Bob', 20, 'Accountant' FROM dual UNION ALL SELECT 3, 'Charles', 30, 'Programmer' FROM dual UNION ALL SELECT 4, 'Dan', 10, 'Manager' FROM dual UNION ALL SELECT 5, 'Eric', 10, 'Salesman' FROM dual UNION ALL SELECT 6, 'Franc', 20, 'Consultant' FROM dual), dept AS (SELECT 10 AS deptno, 'Accounts' AS dname, 100 employment_type_id FROM dual UNION ALL SELECT 20, 'Broking', 100 FROM dual UNION ALL SELECT 30, 'Corporate Relations', 200 FROM dual), employment_type AS (SELECT 100 AS employment_type_id, 'Permanent' AS description FROM dual UNION ALL SELECT 200, 'Contract' FROM dual) /* --- Query 1 select e.ename, d.dname, e.description from emp e inner join dept d on e.deptno = d.deptno inner join employment_type e on d.employment_type_id = e.employment_type_id -- */ -- /* Query 2 SELECT e.ename, d.dname, e.description FROM employment_type e INNER JOIN dept d ON e.employment_type_id = d.employment_type_id INNER JOIN emp e ON d.deptno = e.deptno -- */ ; 

As you can see in both queries, the alias for emp and employment_type tables is the same, i.e. e .

When I select a column saying e.description I should not be mistaken saying something like

ambiguous column reference

Morevoer, the result of two queries is different! In the first, select emp.description , and in the second, employment_type.description .

Please let me know why this happens and how to avoid confusion arising from this.

Answer 1

Oracle SQL has never fully complied with the ANSI / ISO SQL standard. For example, it never supported AS in a from clause:

 select *from dual AS d; -- fails 

The current compliance state ( Oracle Compliance To Core SQL: 2011 for Oracle 12c) indicates that various ANSI SQL functions are supported mostly partially, for example:

 ... E031, Identifiers: Oracle supports this feature, with the following exceptions: ... 

or,

 E051, Basic query specification Oracle fully supports the following subfeatures: ... 

And although he says nothing about ambiguous aliases (or range variables officially), you can anticipate that the differences can easily go deeper than indicated on the page.

Currently, I don’t know how to make Oracle vague in such cases, but just making sure that your aliases differ is not so difficult, in my opinion.

You may wonder if ANSI SQL Standard does not say exactly that duplicate aliases in the same scope are not allowed. Section 7.6 of Part 2 contains the SQL / Foundation SQL Standard: 2011 document. (You can download a draft from www.wiscorp.com ). In particular, in the subsection "Syntax Rules", 10), it says (I redid it a little):

 10) Let RV be a range variable that is exposed by TR. Let RV1 be a range variable that is exposed by a <table reference> TR1 that has the same scope clause as TR. a) If RV is a <table name>, then i) If RV1 is a <table name>, then RV1 shall not be equivalent to RV. ii) Otherwise, RV1 shall not be equivalent to the <qualified identifier> of RV. b) Otherwise i) If RV1 is a <table name>, then the <qualified identifier> of RV1 shall not be equivalent to RV. ii) Otherwise, RV1 shall not be equivalent to RV. 

Here, the RVs are variable ranges, and you can see that the last choice applies to the case with two aliases.

We know that the basic SQL markers implement this check (SQL Server, MySQL, PostgreSQL), so this information must be accurate, despite the fact that it left the project.

Answer 2

You can try the following query to get the result:

This is for: for a description of the emp table

 /* --- Query 1 select e.ename, d.dname, e.description from emp e inner join dept d on e.deptno = d.deptno inner join employment_type et on d.employment_type_id = et.employment_type_id -- */ -- /* Query 2 SELECT e.ename, d.dname, em.description FROM employment_type e INNER JOIN dept d ON e.employment_type_id = d.employment_type_id INNER JOIN emp em ON d.deptno = em.deptno -- */ 

According to your code:

In the first query, you use the alias e for the emp table (like the first table). therefore, it gets the description from the first table you specified (emp table).

Same as query 2: It refers to the jobs_type table (as the first table) so it gets a description from it.