Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Differ bet WHERE clause & INNER JOIN?

    In simple terms, if possible, what is the difference between using the WHERE clause in a SELECT statement vs an INNER JOIN? According to Rob Viera's book the WHERE is "inclusive" in nature, meaning that all records that meet the WHERE condition are included in the result set. The text further stated that an INNER JOIN is "exclusive" in nature meaning that data not meeting the JOIN condition is excluded from the result set.

    In layman's terms, what is the difference? Any examples? Thanks in advance.

    ddave

  2. #2
    Join Date
    Feb 2004
    Location
    Brazil
    Posts
    1

    Inner Join x Clause Where

    So, I saw the question about Inner Join vs Where in Queries. I'm using all the "Joins" in clause where, is there problem to do it or I should be using Inner Join ?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Linking tables should be done using a JOIN clause. Filtering on individual data elements should be done with WHERE clauses.

    For simple queries, the SQL Server Optimizer will convert a statement like

    select * from a, b where a.key = b.key

    into

    select * from a inner join b on a.key = b.key

    ...and will make optimium use of the indexes on the two tables to create an efficient execution plan. For more complicated queries the optimizer may not be able to make this conversion.

    The JOIN syntax leads to better organized and more easily readable and debuggable code, because it clearly establishes the relationships between tables.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    ...and will make optimium use of the indexes on the two tables to create an efficient execution plan. For more complicated queries the optimizer may not be able to make this conversion.
    Ya think? Not so sure...but youcan test it and check out the plan the optimizer chooses by looking at the query plan



    The JOIN syntax leads to better organized and more easily readable and debuggable code, because it clearly establishes the relationships between tables.
    100%
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not so sure it will reevalutate the simple query, or not so sure it won't be able to evaluate a complex query?
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm not sure that it will give you 2 different plans...have you seen this?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It won't for simple queries. I've heard that it might not be able to for complex queries. At some point of complexity there must be a statement that it is beyond its capacity to reduce to simple join clauses. I mean, complexity is theoretically infinite, right? I suspect that it may have difficulty reevaluating WHERE clauses containing conditional syntax, but I haven't formally tested it.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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