I recently ran across an issue with using subqueries, specifically when the subquery refers to a column that isn’t in the subquery table, but is in the parent table.
I was working in Postgres but this same issue comes up in T-SQL as well
DROP TABLE IF EXISTS temp_a; DROP TABLE IF EXISTS temp_b; CREATE TABLE temp_a (code char(4) NOT NULL, name varchar(10) NOT NULL); CREATE TABLE temp_b (code2 char(4) NOT NULL); INSERT INTO temp_a(code, name) VALUES ('A','A'), ('B', 'B'), ('C','C'); INSERT INTO temp_b(code2) VALUES ('A'); SELECT * FROM temp_a WHERE code IN (SELECT code FROM temp_b);
Table temp_b doesn’t have a code column, but temp_a does. At first glance, you would think the subquery would fail:
SELECT code FROM temp_b
The subquery fails on its own. I’m guessing that the query assumes that code refers to the column from the parent table (temp_a), since all of the temp_a records are returned.
Using a table alias in the subquery will raise an error in this situation:
SELECT * FROM temp_a WHERE code IN (SELECT b.code FROM temp_b as b);
Not a common situation, you would have to mix up columns from the two tables, but still something to be aware of. I don’t use subqueries a great deal, but I’ll use a table alias in the subquery going forward,