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”.

About these ads

6 Responses to “ANSI join syntax”


  1. 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…


  2. 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:

    SELECT
    C.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;



  3. 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…


    • 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'

  4. David Grimberg Says:

    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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: