SQL Server Performance Forum – Threads Archive

T-SQL Subquery …Query :S

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!

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’.

ah sorry in wrong forum doesnt in 2000 I will move it
sorry

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.

<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ /> sorry had a long night last night …. its the column that doesnt exist not table! I scanned microsoft site once might go back again and have a look cheers for the reply!

Can you post the actual query?

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.

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

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.

Excellent thanks for that. Cleared it up I expected it would be expected behaviour just didnt fully understand why. <img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />

http://sqlservercode.blogspot.com/2007/04/you-should-never-use-in-in-sql-to-join.html
Madhivanan Failing to plan is Planning to fail

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

Thanks Frank. I just wonder this can happen only in 2000 or other versions as well [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

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.

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

]]>