Postgres subquery has access to column in a higher level table. Is this a bug? or a feature I don't understand?
postgresql
Solution
Although this feature might be confusing, without it, several types of queries would be more difficult, slower, or impossible to write in sql. This feature is called a "correlated subquery" and the correlation can serve a similar function as a join.
For example: Consider this statement
select first_name, last_name from users u
where exists (select * from orders o where o.user_id=u.user_id)
Now this query will get the names of all the users who have ever placed an order. Now, I know, you can get that info using a join to the orders table, but you'd also have to use a "distinct", which would internally require a sort and would likely perform a tad worse than this query. You could also produce a similar query with a group by.
Here's a better example that's pretty practical, and not just for performance reasons. Suppose you want to delete all users who have no orders and no tickets.
delete from users u where
not exists (select * from orders o where o.user_d = u.user_id)
and not exists (select * from tickets t where t.user_id=u.ticket_id)
One very important thing to note is that you should fully qualify or alias your table names when doing this or you might wind up with a typo that completely messes up the query and silently "just works" while returning bad data.
The following is an example of what NOT to do.
select * from users
where exists (select * from product where last_updated_by=user_id)
This looks just fine until you look at the tables and realize that the table "product" has no "last_updated_by" field and the user table does, which returns the wrong data. Add the alias and the query will fail because no "last_updated_by" column exists in product.
I hope this has given you some examples that show you how to use this feature. I use them all the time in update and delete statements (as well as in selects-- but I find an absolute need for them in updates and deletes often)
Problem
I don't understand why the following doesn't fail. How does the subquery have access to a column from a different table at the higher level? ``` drop table if exists temp_a; create temp table temp_a as ( select 1 as col_a ); drop table if exists temp_b; create temp table temp_b as ( select 2 as col_b ); select col_a from temp_a where col_a in (select col_a from temp_b); /*why doesn't this fail?*/ ``` The following fail, as I would expect them to. ``` select col_a from temp_b; /*ERROR: column "col_a" does not exist*/ select * from temp_a cross join (select col_a from temp_b) as sq; /*ERROR: column "col_a" does not exist *HINT: There is a column named "col_a" in table "temp_a", but it cannot be referenced from this part of the query.*/ ``` I know about the LATERAL keyword (link, link) but I'm not using LATERAL here. Also, this query succeeds even in pre-9.3 versions of Postgres (when the LATERAL keyword was introduced.) Here's a sqlfiddle: http://sqlfiddle.com/#!10/09f62/5/0 Thank you for any insights.