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 3 of 3

Thread: JOIN versus AND

  1. #1
    New to the CF scene
    Join Date
    Apr 2011
    Posts
    8
    Thanks
    5
    Thanked 0 Times in 0 Posts

    JOIN versus AND

    When I was learning mysql, my professor taught us to use ANDs to join two columns, though there were times that we needed to use RIGHT JOINS and LEFT JOINS for more complex queries.

    Sample query for first way of doing things with commas and AND
    Code:
     
    SELECT volunteers.fname,volunteers.minit,volunteers.lname,volunteers.address,students.fname,students.minit,students.lname
    FROM volunteers,students,relationships
    WHERE (relationships.volunteer_id=volunteers.volunteer_id AND students.student_id=relationships.student_id) AND relationships.relationship='grandparent';
    Sample query for second way with JOIN and ON
    Code:
    SELECT *  
    FROM   employee  LEFT OUTER JOIN department  
              ON employee.DepartmentID = department.DepartmentID;


    However, while poking online, it seems like JOIN was the thing to do. I've had experience with CS professors being behind on the times and I want to make sure I get things right.
    Last edited by mingqi; 06-20-2011 at 04:55 PM.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,979
    Thanks
    120
    Thanked 76 Times in 76 Posts
    No difference, just another syntax.

    SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation".
    http://en.wikipedia.org/wiki/Join_%28SQL%29
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,525
    Thanks
    80
    Thanked 4,490 Times in 4,454 Posts
    I call joins using only WHERE clauses "implicit joins".

    For all practical purposes, including performance (speed), there is no difference between an implicit join and an INNER join. Use whichever one gives you a warm fuzzy feeling.

    But there is no way to do an OUTER join of any kind using an implicit join. (There used to be, 15 years or so ago, when using MS SQL Server and Oracle, but those methods were deprecated by ANSI and are now obsolete.)

    Personally, I think that a teacher in a decent school should *NOT* teach implicit joins. Not because there is anything evil or wrong with them (I use them more often than not), but just because they don't prepare you for the very different syntax and semantics of outer joins.

    And as long as we are discussing outer joins, look at an old post of mine that may help you avoid problems with outer joins:
    http://www.codingforums.com/showthre...192#post818192


  •  

    Posting Permissions

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