In many columns, select NOT IN.

Problem

The following query must be implemented:

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

But NOT IN can’t be implemented on multiple columns. How do I write this query?

Asked by Gunjan Nigam

Solution #1

I’m not sure whether you’ve considered:

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 related to id1 and id2 is related to id2, not both.

Answered by Michał Powaga

Solution #2

Another RDBMS that is inexplicably unknown. In PostgreSQL, your syntax is correct. Other query patterns (notably the NOT EXISTS variation or an LEFT JOIN) may be faster, but your query is valid.

When using NOT IN with NULL values, be cautious of the following pitfalls:

LEFT JOIN vs. LEFT JOIN vs. LEFT JOIN v

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

For the NOT EXISTS option, see @Micha’s response. A more in-depth examination of four basic variants:

Answered by Erwin Brandstetter

Solution #3

I employ a method that may appear ridiculous, but it works for me. I just concatenate the columns I wish to compare and use NOT IN: as the comparison operator.

SELECT *
FROM table1 t1
WHERE CONCAT(t1.first_name,t1.last_name) NOT IN (SELECT CONCAT(t2.first_name,t2.last_name) FROM table2 t2)

Answered by vacolane

Solution #4

For numerous columns, you should generally use NOT EXISTS.

Answered by Raoul George

Post is based on https://stackoverflow.com/questions/8033604/select-not-in-multiple-columns