Nick <nboutelier(at)gmail(dot)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