David David - 2 years ago 97
SQL Question

Why does a query with an invalid subselect run?

Saw a colleague run a query today and I couldn't explain why or how it didnt fail.
The query was (abstracted):

UPDATE table1
SET columnToUpdate = 1
WHERE recordID IN (
SELECT recordID FROM table2 WHERE table2column IN ( *list of values*))

The problem was he had mistaken recordID, which is only present in table1, for personID, which is present in both tables and is a PK in table 1, FK in table 2.

I would have thought the query would not have run, due to the subselect being unable to resolve (indeed, if you run the subselect alone, it fails).
Instead, running the above query updated all records in table1, which were previously a combination of NULL and 0.

It was simple to fix, but can anyone explain why this behaviour would take place?


Answer Source

It is called Correlated sub-query. It helps you to refer outer query columns inside the sub-query

The recordID column is referred from table1 in the sub-query not from the table2 that is why it is failing when you run the sub-query alone. Normally you can see outer query columns referred in Where clause when EXISTS/NOT EXISTS used

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download