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,