SQL Server Performance

T-SQL Subquery ...Query :S

Discussion in 'SQL Server 2005 General DBA Questions' started by L0st_Pr0phet, Jun 22, 2007.

  1. L0st_Pr0phet Member

    Hi,
    In my haste yeasterday to help out a user I came accross a 'Feature' that actually has caused me lots of work!!

    query:
    select * from table1 where id in (select id from table2)

    Problem table2 does not exist, if you run the subquery on its own it fails, yet if you use the above query it will bring back whole of table1. Obviously I can re-write this query no problem I just wondered if anyone could explain the logic behind it!
  2. smy New Member

    I tried the query. If table2 does not existed, running
    select * from table1 where id in (select id from table2)

    will definitely give error
    Invalid object name 'table2'.
  3. L0st_Pr0phet Member

    ah sorry in wrong forum doesnt in 2000 I will move it
    sorry
  4. Adriaan New Member

    When using SQL 2000, it does bring up the "Invalid object name 'table2'." error message. I tried a few variations, but every time the error was raised.

    There was a situation with invalid column names in subqueries that did not raise any error, but I can't remember the details.
  5. L0st_Pr0phet Member

    <img src='/community/emoticons/emotion-2.gif' alt=':D' /> <br /><br />sorry had a long night last night ....<br />its the column that doesnt exist not table!<br /><br />I scanned microsoft site once might go back again and have a look <br /><br />cheers for the reply!
  6. Adriaan New Member

    Can you post the actual query?
  7. L0st_Pr0phet Member

    yeah sure its below, its very simple query as ya can see, it was just meant to be a quick lookup.

    select *
    from
    contract_Lookup
    where
    contract_no in (select contract_no from Error_contracts)

    problem was the actual column was contract_number in the Error_contracts table.



  8. FrankKalis Moderator

    This is expected behaviour!

    select *
    from
    contract_Lookup
    where
    contract_no in (select contract_no from Error_contracts)

    returns the contract_no for each row in contract_Lookup. As you're not using table aliases and subqueries need to be able to reference columns in the inner AND outer SELECT (otherwise correlated subqueries wouldn't be possible), the result is that contract_no is tried be be resolved as Error_contracts.contract_no. And as doesn't exist it is resolved to contract_Lookup.contract_no. As contract_Lookup.contract_no does exist, the syntax is perfectly valid and no error is thrown. However, on the logical level the query returns completely wrong information.

    So, your query translates now to

    select *
    from
    contract_Lookup
    where
    contract_no in (contract_no)

    The WHERE clause is always true and therefore all rows in contract_Lookup are returned.

    To avoid any ambiguity you are better off qualifying the columns with the tables from which they should come from in cases where more than one table in involved. Suppose you had written this instead:

    select cl.*
    from
    contract_Lookup cl
    where
    cl.contract_no in (select ec.contract_no from Error_contracts ec)

    the parser would have immediately noted it and thrown an error.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  9. Adriaan New Member

    Frank, that was exactly the situation I was thinking of (but couldn't remember).

    The rule is that you should ALWAYS qualify column names with an object reference. The only exception is the column list for target table in an INSERT query, where you would get a syntax error.
  10. L0st_Pr0phet Member

    Excellent thanks for that.<br /><br />Cleared it up I expected it would be expected behaviour just didnt fully understand why.<br /><br /><img src='/community/emoticons/emotion-2.gif' alt=':D' />
  11. Madhivanan Moderator

    http://sqlservercode.blogspot.com/2007/04/you-should-never-use-in-in-sql-to-join.html


    Madhivanan

    Failing to plan is Planning to fail
  12. FrankKalis Moderator

    That blog post generalizes things. There is no binary answer whether to use IN or not. Sure, there are problems at the logical level that one should be aware of when using IN, but these problems are not mentioned anywhere in the link. For a detailed explanation check this out:http://groups.google.de/group/microsoft.public.sqlserver.programming/msg/f530df34d5afe639

    Which one (IN, EXISTS, ...JOIN) actually performs better depends on many factors, but when you are aware of the fact that there is more than one way to skin that cat and you know about the implications of each approach, you're in a position of knowledge from which you can consciously decide which alternative to use.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  13. Madhivanan Moderator

    Thanks Frank. I just wonder this can happen only in 2000 or other versions as well [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  14. Adriaan New Member

    Madhivanan,

    It's a weakness in the SQL standard that you are not obliged to mention the object before the column. The same probably applies to most (if not all) RDBMS platforms.
  15. FrankKalis Moderator

    I don't know too many RDBMS apart from SQL Server that well, but I guess all systems that support correlated subqueries are subject to this issue.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de

Share This Page