ANSI join syntax
March 2, 2011
Here is unconventional way to write a query in ANSI SQL join syntax
SELECT 1
FROM DEPARTMENTS C
JOIN EMPLOYEES A
JOIN JOBS B
ON C.DEPARTMENT_ID = A.DEPARTMENT_ID
ON A.JOB_ID = B.JOB_ID
It is valid syntax according to ANSI 92. Indeed, the fragment
EMPLOYEES A
JOIN JOBS B
ON C.DEPARTMENT_ID = A.DEPARTMENT_ID
is a qualified join which is a joined table which is a table reference.
Therefore, one can just take conventional restrict-project-cartesian product query, and replace all the commas in the FROM clause with the JOIN keyword, then replace WHERE and AND keywords with the ON.
An argument in favor of ANSI style is allegedly cleaner textbook example
SELECT 1
FROM DEPARTMENTS C
JOIN EMPLOYEES A ON C.DEPARTMENT_ID = A.DEPARTMENT_ID
JOIN JOBS B ON A.JOB_ID = B.JOB_ID
although, as we have witnessed, ANSI grammar doesn’t enforce it. A developer is free to mix the first and the second variant in unbelievably messy query!
My feelings is reflected in a quote by Anthony Molinaro (author of “SQL Cookbook”):
“Old style is short and sweet and perfect. ANSI dumbed it down, and for people who’ve been developing for sometime, it’s wholly unnecessary”.
March 5, 2011 at 9:42 am
Note that there is a difference between conditions in ON and WHERE :
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/queries003.htm
In hierarchical queries, a condition in the JOIN clause is evaluated before the CONNECT BY and before a condition in the WHERE clause
Maybe it is a design bug…
March 7, 2011 at 5:40 pm
This seems to be a documentation bug: it was a difference between ANSI and oracle syntax that triggered my post. Here is an example of abused ANSI syntax from stackoverflow:
SELECTC.FullName,
O.OrderTotal,
OD.ExtendedShippingNotes
FROM
Customer C
CROSS JOIN Order O
INNER JOIN OrderDetail OD
ON C.CustomerID = O.CustomerID
AND C.CustomerStatus = 'Preferred'
AND O.OrderTotal > 1000.0
WHERE
O.OrderID = OD.OrderID;
March 8, 2011 at 7:55 am
SQL> select sys_connect_by_path(ename,'/')
2 from emp
3 where ename='SCOTT'
4 connect by prior empno=mgr;
SYS_CONNECT_BY_PATH(ENAME,'/')
---------------------------------------------
/SCOTT
/JONES/SCOTT
/KING/JONES/SCOTT
SQL> select sys_connect_by_path(ename,'/')
2 from emp join dual on ename='SCOTT'
3 connect by prior empno=mgr;
SYS_CONNECT_BY_PATH(ENAME,'/')
---------------------------------------------
/SCOTT
SQL>
If the condition is specified in the “ON” clause, it is executed before the hierarchy…
March 8, 2011 at 8:47 pm
Formally you are correct, although I’d suggest that “connect by” should be deprecated. Mixing conventional joins with iterative joins (AKA transitive closure) in one query block is confusing. I don’t envision any problems with properly written hierarchical query
WITH
org_chart (eid, emp_last, mgr_id, path) AS
(
SELECT empno, ename, mgr, '/'||ename
FROM emp
UNION ALL
SELECT e.empno, e.ename, e.mgr, path || '/' || ename
FROM org_chart r join emp e
on r.eid = e.mgr
)
SELECT path
FROM org_chart join dual
on emp_last = 'SCOTT'
March 9, 2011 at 8:50 am
Indeed, no possible confusion with recursive CTE
April 14, 2011 at 4:32 pm
I tried your first query in the HR schema and got the following result:
SQL> select 1 from departments c
2 join employees a
3 join jobs b
4 on c.department_id = a.department_id
5 on a.job_id = b.job_id
6 /
on c.department_id = a.department_id
*
ERROR at line 4:
ORA-00904: “C”.”DEPARTMENT_ID”: invalid identifier
Oracle obviously does not like this particular bit of code. However, swapping lines 4 and 5 the code will run successfully and demonstrates a nested join which can be very usefull when you need to outer join from table A to the projection of B join C when your join criteria from A includes columns in both B and C e.g.:
Select A.c3, B.c3, C.c3
from A
left join B
join C
on B.c1 = C.c1
on a.c1 = b.c2
and a.c2 = c.c2;
In this example you would get all records from A and only get records from B and C when B joins with C.