Postgres Where Subquery

It's not a bug, it's a feature #

So while poking around at a bug in one of our database views, I looked at
another view that queried it and happened to find some code like this:

    SELECT col1
    FROM right_table R
    WHERE r.col1 IN (
        SELECT DISTINCT r.col1
        FROM wrong_table
    );

Where wrong_table doesn't have col1. Now this code has been in production
for months now, and no bugs were found from it and it all worked well. The
query above will select all rows of right_table unless wrong_table is
empty, in which case it selects no rows. This code gave no errors in
production because the table that the view is based on already filters for the
result, but that wasn't ideal.

    SELECT col1
    FROM right_table r
    WHERE r.col IN (
        SELECT w.col
        FROM wrong_table w
    );

← Home