Performing antijoin in postgres

Suresh_ wrote:

I want to add an antijoin operator to PostgreSql. Basically I want to store
the result (tupletableslot) of one join and then compare it with
another..How do I store these results and how do I compare them ?

I would suggest implementing it at a higher level, rewriting a query
like "A ANTIJOIN B" to "A EXCEPT (A JOIN B)". That allows the planner to
use whatever methods it has to perform the EXCEPT and the JOIN
operations. At the moment there's only one method: sort the inputs and
do a merge, but it's conceivable that you could implement EXCEPT using
hashing, for example.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

"Suresh_" <suiyengar@yahoo.com> writes:

I want to add an antijoin operator to PostgreSql.

I think you can already do what you want like this:

SELECT *
FROM A FULL OUTER JOIN B ON (x=y)
WHERE x IS NULL
OR y IS NULL

So for example:

postgres=# select * from a;
i
---
1
2
(2 rows)

postgres=# select * from b;
i
---
2
3
(2 rows)

postgres=# select * from a full outer join b on (a.i=b.i) where a.i is null or b.i is null;
i | i
---+---
1 |
| 3
(2 rows)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Thanks for the reply. I can do this at the higher level; but this antijoin
thing is a small part of a bigger project. So I want to do it internally in
one of the joins. Does anyone have experience in storing tupleslpts and
comparing them ??

Gregory Stark wrote:

"Suresh_" <suiyengar@yahoo.com> writes:

I want to add an antijoin operator to PostgreSql.

I think you can already do what you want like this:

SELECT *
FROM A FULL OUTER JOIN B ON (x=y)
WHERE x IS NULL
OR y IS NULL

So for example:

postgres=# select * from a;
i
---
1
2
(2 rows)

postgres=# select * from b;
i
---
2
3
(2 rows)

postgres=# select * from a full outer join b on (a.i=b.i) where a.i is
null or b.i is null;
i | i
---+---
1 |
| 3
(2 rows)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
View this message in context: http://www.nabble.com/Performing-antijoin-in-postgres-tf4358905.html#a12425655
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.