Results 1 to 10 of 10

Thread: SQL Question

  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: SQL Question

    Can someone tell me which of these two approaches are better when writing SQL. Here's a simple example.

    Example1:

    SELECT employee.employee_id
    FROM employee
    INNER JOIN company
    ON employee.company_id = company.company_id
    AND company.company_id = 1

    Example2:

    SELECT employee.employee_id
    FROM employee
    INNER JOIN company
    ON employee.company_id = company.company_id
    WHERE company.company_id = 1

  2. #2
    Join Date
    May 2004
    Location
    Ohio
    Posts
    61
    in my opinion, example 2

  3. #3
    Join Date
    May 2004
    Location
    Ohio
    Posts
    61
    in my opinion, example 2

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    IMHO the first one is always better, especially if you have an index that has both fields.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Sep 2003
    Posts
    364
    The major difference I've found is when the INNER JOIN is a LEFT or RIGHT JOIN instead.
    Example 2 won't return the desired results in these cases.

    Example1:

    SELECT employee.employee_id
    FROM employee
    LEFT JOIN company
    ON employee.company_id = company.company_id
    AND company.company_id = 1

    Example2:

    SELECT employee.employee_id
    FROM employee
    LEFT JOIN company
    ON employee.company_id = company.company_id
    WHERE company.company_id = 1

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Example 2 has my vote, simply because it clearly separates the relationional component of the statement from the filtering component. I only use the first syntax in the case of outer joins.

    But if this is the biggest dilemma you are facing in your development, then I want to work on YOUR project.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Posts
    492
    The joins are executed prior the where, so sql can come up with a different plan. The LEFT-join makes it a diffrent qry: in a lef/right join those without a match are NULL including company.company_id <> 1.

    What are the desired results?

  8. #8
    Join Date
    Sep 2003
    Posts
    364
    Blindman, unfortunately this isn't the biggest dilemma we're facing it's just Friday and I'm hungover. So if I worked on anything important I'd probably screw it up.

    Kaiowas, sorry i forgot to add 'WHERE company.company_id IS NULL'. In my case I want to find every employee that isn't associated with company 1.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    SELECT employee.employee_id
    FROM employee
    INNER JOIN company
    ON employee.company_id = company.company_id
    AND company.company_id != 1
    --in order to have a "balanced JOIN" you need to add another line here
    AND employee.company_id != 1
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Sep 2003
    Posts
    364
    Geez, I just re-read my post and my example really sux. I think it's time to go home.

Posting Permissions

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