Postgresql – way to alias columns for reference in subqueries

postgresqlsubquery

I have a SQL statement in PostgreSQL that takes the following form:

SELECT * FROM db WHERE (b + 1) IN (SELECT b FROM db WHERE a = 7)

This query returns each record whose b value, incremented by 1, is in the set of all b values of the records matching a = 7. However, I'm looking for a way to write this in the form of an EXISTS statement, since that may be more optimal. Something of the form

SELECT * FROM db WHERE EXISTS (SELECT 1 FROM db WHERE a = 7 and b = b* + 1)

where b* somehow references the b attribute not inside the subquery, but outside it. Is there a way to write the first query without IN and with EXISTS?

Best Answer

You need a table alias for both occurrences of the table and then prefix every column with the corresponding table alias to make the column references unambiguous.

select t1.*
from the_table t1
where exists (select *
              from the_table t2
              where t2.a = 7 
                and t2.b = t1.b + 1);

Note that you probably want something like and t2.primary_key <> t1.primary_key in the sub-select as well.

Related Question