Geek asks and answers

Why is this SQL code not generating an error?

Possible duplicate:
Column does not exist in IN clause, but SQL works

I came across this today, when at work today, and I was wondering why the following code is not generated and error?

CREATE TABLE #TableA (ColumnA VARCHAR(25))
CREATE TABLE #TableB (ColumnB VARCHAR(25))

INSERT INTO #TableA (ColumnA) VALUES('1')
INSERT INTO #TableA (ColumnA) VALUES('2')
INSERT INTO #TableA (ColumnA) VALUES('3')

INSERT INTO #TableB (ColumnB) VALUES('1')

SELECT *
FROM #TableA
WHERE ColumnA IN(SELECT ColumnA FROM #TableB)

Conclusion:

ColumnA
1
2
3

ColumnA does not exist in #TableB, how does an error not occur?

@@ VERSION tells me that I am running this at:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)   Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)
+5
source share
1 answer

ColumnAin the subquery (SELECT ColumnA FROM #TableB) refers to ColumnA#TableA, which is a valid column in the SELECT list.

Therefore, there is no error, and you get three rows as you compare # TableA.ColumnA to C # TableA.ColumnA

, ColumnA -, , (, ColumnAB), .

:

CREATE TABLE #TableA (ColumnA VARCHAR(25))
CREATE TABLE #TableB (ColumnB VARCHAR(25))

INSERT INTO #TableA (ColumnA) VALUES('1')
INSERT INTO #TableA (ColumnA) VALUES('2')
INSERT INTO #TableA (ColumnA) VALUES('3')

INSERT INTO #TableB (ColumnB) VALUES('1')

SELECT *
FROM #TableA
WHERE ColumnA IN(SELECT ColumnAB FROM #TableB)

Msg 207, 16, 1, 14
"ColumnAB".

+10

More articles:


All Articles