How to SELECT a.* FROM a WHERE EXCEPT SELECT b.* FROM b WHERE a.id != b.id

I am having quite a struggle with the presented postgres request.

I have a table objects with a few columns, including an id column. I have a table object_couples that references couples of objects with id. This table contains in consequence 2 columns of ids.

I have an external variable, like int external_variable = 42.

I am trying to select every entry of the objects table where the id of the selected object and the id of the external_variable does not exist as a couple in the object_couples table.

My request looks like the following :

SELECT id, c1, c2 FROM objects WHERE condition1 AND condition2
EXCEPT SELECT left_id, right_id FROM object_couples WHERE objects.id != object_couples.left_id AND external_variable != object_couples.right_id;

What can I do?

EDIT 1 :The following request is not rejected but causes in pycharm a code 137(SIGKILL) :

SELECT id, c1, c2 FROM objects AS S
INNER JOIN object_couples ON object_couples.left_id != S.id AND object_couples.right_id != external_variable
WHERE S.c1 > 1234 AND S.c2 < 5678

1 Answer

I am thinking not exists:

select o.*
from objects o
where not exists (select 1 from object_couples oc where (oc.id = oc.left and 42 = oc.right) or (oc.id = oc.right and 42 = oc.left) );

For performance, you might find that this works better:

select o.*
from objects o
where not exists (select 1 from object_couples oc where oc.id = oc.left and 42 = oc.right ) and not exists (select 1 from object_couples oc where oc.id = oc.right and 42 = oc.left );

In particular if you have indexes on object_couples(left, right) and object_couples(right, left) then this might even be fast.