Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Jun 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    oracle join syntax (old vs new)

    i'm an oracle developer and use the old join syntax, eg:

    select *
    from emp, dept
    where emp.deptno = dept.deptno;


    I've noticed that mysql uses the newer (oracle 9i and up) method of joining tables, eg:

    select ename, dname, emp.deptno, dept.deptno
    from SCOTT.EMP inner join SCOTT.DEPT
    on emp.deptno = dept.deptno


    Does mysql support the old oracle method of joining tables? I've struggle to get my head around the newer INNER JOIN, FULL OUTER JOIN etc syntax

    thanks
    Last edited by vinyl-junkie; 06-17-2013 at 01:27 PM. Reason: signatures only allowed through the control panel

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Yes and no.

    MySQL (and *ALL* ANSI-compliant databases) accept the use of
    Code:
    select list, of, fields
    from emp, dept 
    where emp.deptno = dept.deptno;
    But none of them support the old Oracle (and SQL Server and other ancient versions of various relational database engines) syntax for OUTER joins.

    That is, you *MUST* convert
    Code:
    select list, of, fields
    from emp, dept 
    where emp.deptno *= dept.deptno;
    into
    Code:
    select list, of, fields
    from emp LEFT JOIN dept 
    ON emp.deptno = dept.deptno;
    Odds are pretty good that those are the only two kinds of joins you will really need, so you can stick to the old version for INNER joins and only have to learn the syntax for left joins.

    CAUTION: One reason that *= went out of style is because of the problems associated with it. LEFT JOIN fixes those problems, so long as you take a reasonable modicum of care. Read this old post of mine (which is a copy of a post I made almost 10 years ago in another forum, a SQL Server forum, in fact):
    http://www.codingforums.com/showthre...192#post818192
    Last edited by Old Pedant; 06-17-2013 at 06:59 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Jun 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks for the response. So presumably the (+) outerjoin syntax isnt supported either, eg:

    Code:
    select list, of, fields
    from emp, dept 
    where emp.deptno (+) = dept.deptno ;

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Sorry, I used *= because that's what I remembered the old syntax to be. If it's += then that's what I should have used.

    In any case none of those, other than just =, are supported any more. (And are deprecated even in Oracle, you know.)

    The reason they don't work is that there is no way to handle the example that I showed: Where an outer join gets incorrectly converted to an inner join. That is, there is no way to distinguish between the conditions that create the join and the conditions outside of the join. Using the LEFT JOIN (or RIGHT JOIN, but that's just syntactic sugar...all RIGHT JOINs can easily be converted to LEFT JOINs) syntax, the ON keyword allows you to separate JOIN conditions from common WHERE conditions.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •