0

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.