Posted by Hugo Kornelis on 07/05/06 22:56

On 5 Jul 2006 15:30:42 -0700, Xeth Waxman wrote:

>Good afternoon,
>
>I have a bizarre question. When running the following query:
>
>select SomeColumnName from TableA where PK_TableA in
>(select PK_TableA from TableB)
>
>I get results. This should not be feasible, because the query within
>the in clause:
>
>select PK_TableA from TableB
>
>is not possible - there is no PK_TableA column within the TableB table.
> Running the sub-query alone gives an error, but when using it as
>sub-query in the first statement, I get every row within TableA.
>
>If it helps any, the exact query I'm running is:
>
>select demonstratorid from DirectSalesAgent where DirectSalesAgentId in
>(select directsalesagentid from WebsiteSubscriptionPayment)
>
>Shouldn't a query return an error if the sub-query has an invalid
>column name?

Hi Xeth,

There are two types of subqueries: correlated and uncorrelated. The
correlated ones refer to columns in the outer query.

If a column name in a subquery is not qualified with table name (or
table alias), SQL Server will first test if it matches a column from a
table used in the subquery. If it doesn't, it will then go on and check
if it matches a column in one of the tables in the outer table, assuming
you want a correlated subquery if it does.

The query you wriite is equivalent to this one (adding table qualifiers
for clarity):

SELECT TableA.SomeColumnName
FROM TableA
WHERE TableA.PK_TableA IN (SELECT TableA.PK_TableA
FROM TableB)

This will return all rows from TableA if at least one row exists in
TableB, or no rows at all if TableB is empty. (If TableB is not empty,
the subqeury will have one row for each row in TableB, but the only
column will have the value of TableA.PK_TableA in each of those rows).

--
Hugo Kornelis, SQL Server MVP

[Back to original message]