fractor fractor - 1 month ago 8
SQL Question

INSERT INTO allows SELECT using non-existent column

When executing the following:

CREATE TABLE TableA
(
A1 INT,
A2 INT
)

INSERT INTO TableA (A1, A2) VALUES (1, 2), (3, 4);

CREATE TABLE #TempTable1 (ColumnA INT, ColumnB INT);
CREATE TABLE #TempTable2 (ColumnA INT, ColumnB INT);

INSERT INTO #TempTable1 (ColumnA, ColumnB) VALUES (1, 2);

INSERT INTO #TempTable2
SELECT A1, A2
FROM TableA
WHERE A1 IN (SELECT ColumnA FROM #TempTable1);

SELECT * FROM TableA;
SELECT * FROM #TempTable1;
SELECT * FROM #TempTable2;


The results are as follows:

TableA
A1 A2
1 2
3 4

#TempTable1
ColumnA ColumnB
1 2

#TempTable2
ColumnA ColumnB
1 2


However, if I change the #TempTable2 insert statement so that it selects a non-existent column A1 from #TempTable1:

INSERT INTO #TempTable2
SELECT A1, A2
FROM TableA
WHERE A1 IN (SELECT A1 FROM #TempTable1);


Then #TempTable2 contains all the data from TableA:

ColumnA ColumnB
1 2
3 4


I am wondering why the execution of the INSERT statement doesn't generate an error as the column A1 does not exist in #TempTable1. For example, if I try to add the following statement:

SELECT A1 FROM #TempTable1;


I get:

Invalid column name 'A1'.

Answer

It is called correlated sub-query. Referring the outer query column inside Sub-Query

The column A1 is referred from the outer query (ie) TableA. So when you execute the query together it is working and when you execute alone it is not working

It will be interpreted as

SELECT A1, A2
FROM TableA A
WHERE A1 IN (SELECT A.A1  -- Here check the alias A
             FROM #TempTable1);

Normally when using EXISTS/NOT EXISTS the query will be correlated in where clause.

select * from tableA A 
where exists (select 1 from tableB B 
              where A.Id = B.Id  -- A.Id is referred from tableA
              )

In your example it is referred in Select