SQL Server Performance

SQL 2000 Sub Query bug

Discussion in 'General Developer Questions' started by vimalpercy, Feb 24, 2009.

  1. vimalpercy New Member

    Hello Friends,
    I just want to share with a bug which i have found. Some of you may know this. But curious to know if this issue has been addressed before.
    Say you have 2 tables TableA and TableB
    Say TableA has a field called TableAID and TableB has a field called TableBID
    Add some records and try this query
    Select * from TableA where tableAID in (Select TableAID from TableB)
    I am using wrong Column in TableB in subquery. Instead of throwing an error the SQL return all records in TableA
    Looking forward to your comments
  2. Adriaan New Member

    This is not a bug, but a well-known mistake that people make in SQL syntax ...
    You should always prefix columns with either the table name, or an alias. Without the prefix, you will not get the error, because SQL Server is "smart" enough to identify the freewheeling column name in the subquery as existing in the table mentioned in the main query. (Same reason why you can use correlated subqueries.)
    So if you would write this out in proper SQL syntax, like this:

    SELECT a.* FROM dbo.TableA a WHERE a.tableAID IN (SELECT b.tableAID FROM dbo.TableB b)
    ... indeed you would get the error that there is no column called tableAID in TableB, instead of the unwanted results.
    So use the proper syntax.
  3. vimalpercy New Member

    Hello Adriaan
    Thanks for the reply. I always use prefixed columns, but I got this error when I was doing some testing on the query analyzer.
    So thought of putting this in this forum so that others don't do this mistake when they are creating queries.
  4. FrankKalis Moderator

    Vielleicht hilft das weiter: http://www.insidesql.org/beitraege/entwicklung/unterabfragen-und-namensaufloesung
  5. vimalpercy New Member

    Halo FrankKalis,
    Guten Tag. Ich Danke Ihnen fuer die link.
    Grus
    vimalpercy

Share This Page