Select NOT IN Multiple Columns

Question

I need to execute the following query

SELECT * FROM friend WHERE ( friend.id1, friend.id2 ) NOT IN (SELECT id1, id2 FROM likes) 

but NOT IN cannot be implemented on multiple columns. How to write this request

Answer 1

I'm not sure if you are thinking about:

 select * from friend f where not exists ( select 1 from likes l where f.id1 = l.id and f.id2 = l.id2 ) 

it only works if id1 is associated with id1 and id2 with id2 not both.

Answer 2

Another mysteriously unknown RDBMS. Your syntax is great for PostgreSQL. Other query styles may be faster (especially the NOT EXISTS or LEFT JOIN option), but your query is completely correct.

Remember traps with NOT IN , although when using any NULL values:

• Find records in which the connection does not exist

Option with LEFT JOIN:

 SELECT * FROM friend f LEFT JOIN likes l USING (id1, id2) WHERE l.id1 IS NULL; 

See @ Michał answer for the NOT EXISTS option.
A more detailed assessment of the four main options:

• Select rows not in another table.

Answer 3

You should probably use NOT EXISTS for multiple columns.