CODEWITHSUNDEEP
sqldatabasemultiple-columns
Gunjan Nigam
Gunjan Nigam

Reputation: 1393

Select NOT IN multiple columns

I need to implement the following query:

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?

Upvotes: 115

Views: 205756

Answers (4)

vacolane
vacolane

Reputation: 167

I use a way that may look stupid but it works for me. I simply concat the columns I want to compare and use NOT IN:

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)

Upvotes: 10

Raoul George
Raoul George

Reputation: 2797

You should probably use NOT EXISTS for multiple columns.

Upvotes: -5

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657972

Another mysteriously unknown RDBMS. Your Syntax is perfectly fine in PostgreSQL. Other query styles may perform faster (especially the NOT EXISTS variant or a LEFT JOIN), but your query is perfectly legit.

Be aware of pitfalls with NOT IN, though, when involving any NULL values:

Variant with LEFT JOIN:

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

See @Michał's answer for the NOT EXISTS variant.
A more detailed assessment of four basic variants:

Upvotes: 24

Michał Powaga
Michał Powaga

Reputation: 23183

I'm not sure whether you think about:

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

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

Upvotes: 147

Related Questions