Prev: To update a clr dll should I drop the assembly?
Next: Help us understand this sql script built by a vendor
From: Garren on 1 Jul 2008 11:59
CREATE TABLE #TEST_ONE (
ID INT IDENTITY(1,1) NOT NULL,
TEST_ONE_ITEM VARCHAR(30) NULL
)

CREATE TABLE #TEST_TWO (
ID INT IDENTITY(1,1) NOT NULL,
TEST_TWO_ITEM VARCHAR(30) NULL
)

-- Why does this not fail? TEST_ONE_ITEM clearly doesn't exist in the
#TEST_TWO table. Running the subquery alone will fail, but as a
subquery it simply returns an empty result set.
SELECT * FROM #TEST_ONE WHERE TEST_ONE_ITEM IN(
SELECT TEST_ONE_ITEM FROM #TEST_TWO WHERE ID = 1
)

-- In order to get the query to fail I have to alias the the table in
the subquery. Why?
SELECT * FROM #TEST_ONE WHERE TEST_ONE_ITEM IN(
SELECT T.TEST_ONE_ITEM FROM #TEST_TWO T WHERE ID = 1
)
From: Tibor Karaszi on 1 Jul 2008 12:08
Because the inner query then refers to the outer query (i.e., #TEST_ONE.TEST_ONE_ITEM). This is the
basis for correlated subqueries, and the mistakle you post here is one good reason to always qualify
the tables as soon as there is > 1 table involved in a query).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Garren" <agarren(a)gmail.com> wrote in message
news:fd3b7ee2-aacd-4346-a718-932f214d79e1(a)79g2000hsk.googlegroups.com...
> CREATE TABLE #TEST_ONE (
> ID INT IDENTITY(1,1) NOT NULL,
> TEST_ONE_ITEM VARCHAR(30) NULL
> )
>
> CREATE TABLE #TEST_TWO (
> ID INT IDENTITY(1,1) NOT NULL,
> TEST_TWO_ITEM VARCHAR(30) NULL
> )
>
> -- Why does this not fail? TEST_ONE_ITEM clearly doesn't exist in the
> #TEST_TWO table. Running the subquery alone will fail, but as a
> subquery it simply returns an empty result set.
> SELECT * FROM #TEST_ONE WHERE TEST_ONE_ITEM IN(
> SELECT TEST_ONE_ITEM FROM #TEST_TWO WHERE ID = 1
> )
>
> -- In order to get the query to fail I have to alias the the table in
> the subquery. Why?
> SELECT * FROM #TEST_ONE WHERE TEST_ONE_ITEM IN(
> SELECT T.TEST_ONE_ITEM FROM #TEST_TWO T WHERE ID = 1
> )

From: Plamen Ratchev on 1 Jul 2008 12:16
The query does not fail because it is a valid query. The column name in the
subquery resolves to the column name in the outer query where this is a
valid column for your first table. When you alias the table and use the
alias to prefix the column it results in error because the table alias
references the table that does not have the column.

This is why it is a best practice to prefix all columns in a subquery with
the table alias.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: --CELKO-- on 1 Jul 2008 13:28
Here is how the compiler read your code and qualified the column
names:

SELECT T1.*
FROM #Test_one AS T1
WHERE test_one_item
IN(SELECT T1.test_one_item
FROM #test_two AS T2
WHERE T2.id = 1);

SQL follows pretty much the rules you would expect in any block-
structured language. Look for a local variable and use it; if you
fail to find it, then go one nesting level up; repeat until you get a
hit or failure. There are some more rules about what names are
exposed when you create a derived table.
 | 
Pages: 1
Prev: To update a clr dll should I drop the assembly?
Next: Help us understand this sql script built by a vendor