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?
This question is related to
sql
database
multiple-columns
You should probably use NOT EXISTS
for multiple columns.
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 @Michal's answer for the NOT EXISTS
variant.
A more detailed assessment of four basic variants:
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.
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)
Source: Stackoverflow.com