Forum Stats

  • 3,854,197 Users
  • 2,264,340 Discussions
  • 7,905,603 Comments

Discussions

Difference between old JOIN and ANSI JOIN syntax

User_TDDEP
User_TDDEP Member Posts: 86 Blue Ribbon

Could you please explain me how to rewrite (+) old JOIN sysntax to ANSI JOIN syntax?

I tried like this but it is not working for me for more then two tables:

e.g.:

SELECT E.*, D.*, J.*

FROM EMPLOYEES E, DEPARTMENTS D, JOBS J

WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID

AND E.JOB_ID(+)=J.JOB_ID

AND E.JOB_ID(+)='HR REP'

ORDER BY E.DEPARTMENT_ID;


Please help me !

Best Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,501 Red Diamond
    Answer ✓

    Hi, @User_TDDEP

    In the query you posted, there is no relationship between departments and jobs, that is, there is a cross-join; then employees is outer-joined to that result set. Here's one way to do it in ANSI notation:

    SELECT	   e.*, d.*, j.*
    FROM	   departments  d
    CROSS JOIN jobs		j
    LEFT JOIN  employees    e ON  e.department_id = d.department_id
       	 	    	  AND e.job_id	      = 'HR REP'
    			  AND j.job_id	      = e.job_id
    ORDER BY   e.department_id
    ;
    


    User_TDDEP
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,501 Red Diamond

    Hi, @User_TDDEP

    The two queries you posted half an hour ago are equivalent. The conditions

    AND E.JOB_ID(+)='FI_ACCOUNT'

    AND E.LAST_NAME='Lorentz' 

    are fundamentally different. The former, using (+), means "don't exclude rows just because of the job_id", but the latter means "do exclude rows if they don't have the right last_name". Another way to get the same results is

    SELECT   e.*, d.*, j.*
    FROM 	 departments d
    JOIN	 employees   e   ON e.department_id = d.department_id
    JOIN 	 jobs 	     j	 ON j.job_id 	    = e.job_id
    WHERE 	 e.job_id    = 'FI_ACCOUNT'
    AND	 e.last_name = 'Lorentz'
    ORDER BY e.department_id
    ;
    

    using inner joins

    User_TDDEP
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,501 Red Diamond
    Answer ✓

    Hi, @User_TDDEP

    What about rewriting this one to ANSI JOIN e.g.

    Here's one way, that matches the query you posted very closely:

    SELECT    ts.employee_id
    FROM	  (
    	   SELECT   e.*
    	   --	   ,		... add columns from tables d and j if wanted
    	   FROM   	departments d
    	   CROSS JOIN	jobs	    j
    	   LEFT JOIN employees      e   ON  e.department_id = d.department_id
    	   	    		        AND e.job_id  	    = 'FI_ACCOUNT'
    	   			        AND e.job_id        = j.job_id
    	   WHERE   e.last_name = 'Lorentz' --added cond.
    	  ) ts
    LEFT JOIN job_history jh ON jh.employee_id = ts.employee_id
    ;
    


    It's interesting that the query you posted runs at all, since ts duplicate column names. For some reason, the query I posted raises a "ORA-00918: column ambiguously defined" error if ts has duplicate column names, even though those columns are not used anywhere in the query. If you really want to, you can add all the columns from d and j to the result set of ts, just make sure they have distinct aliases.

    Again, since you're essentially doing an inner join with the employees table, the query above is just a convoluted way of saying:

    SELECT   e.employee_id
    FROM	 departments  d
    JOIN	 employees    e  ON  e.department_id  = d.department_id
    	   	         AND e.job_id	      = 'FI_ACCOUNT'
    JOIN   jobs           j  ON  j.job_id         = e.job_id
    LEFT JOIN job_history jh ON  jh.employee_id   = e.employee_id
    WHERE   e.last_name = 'Lorentz' --added cond.
    ;
    


    User_TDDEP

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,501 Red Diamond
    Answer ✓

    Hi, @User_TDDEP

    In the query you posted, there is no relationship between departments and jobs, that is, there is a cross-join; then employees is outer-joined to that result set. Here's one way to do it in ANSI notation:

    SELECT	   e.*, d.*, j.*
    FROM	   departments  d
    CROSS JOIN jobs		j
    LEFT JOIN  employees    e ON  e.department_id = d.department_id
       	 	    	  AND e.job_id	      = 'HR REP'
    			  AND j.job_id	      = e.job_id
    ORDER BY   e.department_id
    ;
    


    User_TDDEP
  • User_TDDEP
    User_TDDEP Member Posts: 86 Blue Ribbon

    As you wrote - it is a cartesian product.

    ok - now it is clear for me.

    and in case of simple condition

    SELECT E.*, D.*, J.*

    FROM EMPLOYEES E, DEPARTMENTS D, JOBS J

    WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID

    AND E.JOB_ID(+)=J.JOB_ID

    AND E.JOB_ID(+)='FI_ACCOUNT'

    AND E.LAST_NAME='Lorentz' --added cond.

    ORDER BY E.DEPARTMENT_ID;

    ;

    the AND E.LAST_NAME='Lorentz' --added cond.

    will be rewrite to the separeted WHERE clause like this one true?


    SELECT e.*, d.*, j.*

    FROM departments d

    CROSS JOIN jobs j

    LEFT JOIN employees e 

    ON (e.department_id = d.department_id AND e.job_id = 'FI_ACCOUNT' AND j.job_id = e.job_id)

    WHERE E.LAST_NAME='Lorentz'

    ORDER BY e.department_id

    ;

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,501 Red Diamond

    Hi, @User_TDDEP

    The two queries you posted half an hour ago are equivalent. The conditions

    AND E.JOB_ID(+)='FI_ACCOUNT'

    AND E.LAST_NAME='Lorentz' 

    are fundamentally different. The former, using (+), means "don't exclude rows just because of the job_id", but the latter means "do exclude rows if they don't have the right last_name". Another way to get the same results is

    SELECT   e.*, d.*, j.*
    FROM 	 departments d
    JOIN	 employees   e   ON e.department_id = d.department_id
    JOIN 	 jobs 	     j	 ON j.job_id 	    = e.job_id
    WHERE 	 e.job_id    = 'FI_ACCOUNT'
    AND	 e.last_name = 'Lorentz'
    ORDER BY e.department_id
    ;
    

    using inner joins

    User_TDDEP
  • Stax
    Stax Member Posts: 171 Bronze Badge

    Hi @User_TDDEP

    As you wrote - it is a cartesian product.

    so it should be (the script needs to be changed/improved if necessary)

    DEPARTMENTS and JOBS are not related, so Cartesian

    magic data E.JOB_ID='HR REP' one row

    in the older oracle version we will get an error

    SQL> /
    WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID
                            *
    ERROR at line 12:
    ORA-01417: a table may be outer joined to at most one other table
    

    see example (DEPARTMENTS and JOBS have only 2 rows)

    with EMPLOYEES (DEPARTMENT_ID,JOB_ID,ENAME)  as
      (select 10,'HR REP','JONES' from dual 
      )
    ,DEPARTMENTS (DEPARTMENT_ID,DNAME) as
      (select 10,'Administration' from dual union all
       select 210,'IT Support' from dual
      )
    ,JOBS (JOB_ID,JOB_TITLE) as (
      select 'HR REP','HRR'  from dual union all
      select 'IT_PROG','Programmer'  from dual 
      )
    SELECT J.*,E.*, D.*
    FROM EMPLOYEES E, DEPARTMENTS D, JOBS J
    WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID
    AND E.JOB_ID(+)=J.JOB_ID
    AND E.JOB_ID(+)='HR REP'
    ORDER BY E.DEPARTMENT_ID
    /
    
    JOB_ID	JOB_TITLE	DEPARTMENT_ID	JOB_ID	ENAME	DEPARTMENT_ID	DNAME
    HR REP	HRR		10		HR REP	JONES	10		Administration
    IT_PROG	Programmer	-		-	-	210		IT Support
    HR REP	HRR		-		-	-	210		IT Support
    IT_PROG	Programmer	-		-	-	10		Administration
    
    
    4 rows returned in 0.00 seconds	        	Download
    
    
    
  • User_TDDEP
    User_TDDEP Member Posts: 86 Blue Ribbon

    What about rewriting this one to ANSI JOIN e.g.

    select ts.employee_id from JOB_HISTORY jh,

    (SELECT E.*, D.*, J.*

    FROM EMPLOYEES E, DEPARTMENTS D, JOBS J

    WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID

    AND E.JOB_ID(+)=J.JOB_ID

    AND E.JOB_ID(+)='FI_ACCOUNT'

    AND E.LAST_NAME='Lorentz' --added cond.

    ) ts

    where jh.employee_id(+)=ts.employee_id;

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,501 Red Diamond
    Answer ✓

    Hi, @User_TDDEP

    What about rewriting this one to ANSI JOIN e.g.

    Here's one way, that matches the query you posted very closely:

    SELECT    ts.employee_id
    FROM	  (
    	   SELECT   e.*
    	   --	   ,		... add columns from tables d and j if wanted
    	   FROM   	departments d
    	   CROSS JOIN	jobs	    j
    	   LEFT JOIN employees      e   ON  e.department_id = d.department_id
    	   	    		        AND e.job_id  	    = 'FI_ACCOUNT'
    	   			        AND e.job_id        = j.job_id
    	   WHERE   e.last_name = 'Lorentz' --added cond.
    	  ) ts
    LEFT JOIN job_history jh ON jh.employee_id = ts.employee_id
    ;
    


    It's interesting that the query you posted runs at all, since ts duplicate column names. For some reason, the query I posted raises a "ORA-00918: column ambiguously defined" error if ts has duplicate column names, even though those columns are not used anywhere in the query. If you really want to, you can add all the columns from d and j to the result set of ts, just make sure they have distinct aliases.

    Again, since you're essentially doing an inner join with the employees table, the query above is just a convoluted way of saying:

    SELECT   e.employee_id
    FROM	 departments  d
    JOIN	 employees    e  ON  e.department_id  = d.department_id
    	   	         AND e.job_id	      = 'FI_ACCOUNT'
    JOIN   jobs           j  ON  j.job_id         = e.job_id
    LEFT JOIN job_history jh ON  jh.employee_id   = e.employee_id
    WHERE   e.last_name = 'Lorentz' --added cond.
    ;
    


    User_TDDEP