On 11 November 2013 14:34, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Colin 't Hart" <colinthart(at)gmail(dot)com> writes:
>> I can't get Postgresql to execute a query with EXCEPT (or INTERSECT)
>> as an anti-join (or join).
>
>> Is this even possible?
>
> No, and it probably won't ever be, since the semantics aren't the same.
> EXCEPT/INTERSECT imply duplicate elimination.
Can't we just use DISTINCT for that?
Given a query
<query_1> EXCEPT <query_2>
isn't it always possible to rewrite this as
select distinct * from (<query_1>) q1 where not exists (select 1 from
(<query_2>) q2 where q1.col1 = q2.col1 and q1.col2 = c2.col2 and ...
and q1.colN = q2.colN)
?
Regards,
Colin