Thread: How to avoid multiple table scan with "NOT IN"

Is there any way this query could be written that doesnt scan the
subquery table twice?

SELECT * FROM my_table
WHERE (one,two) NOT IN (SELECT sub_one, sub_two FROM my_sub_table)
AND (two,one) NOT IN (SELECT sub_one, sub_two FROM my_sub_table)

Nick <nboutelier@gmail.com> writes:
> Is there any way this query could be written that doesnt scan the
> subquery table twice?

> SELECT * FROM my_table
> WHERE (one,two) NOT IN (SELECT sub_one, sub_two FROM my_sub_table)
> AND (two,one) NOT IN (SELECT sub_one, sub_two FROM my_sub_table)

The first kluge that comes to mind is

SELECT * FROM my_table
WHERE
  (least(one,two), greatest(one,two))
  NOT IN (SELECT least(sub_one,sub_two), greatest(sub_one,sub_two)
          FROM my_sub_table);

assuming that both the columns are of the same sortable datatype.
There are probably other ways.

            regards, tom lane