Select NOT IN multiple columns

Question

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

User · Answer

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

User · Answer

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    Find records where join doesn  39 t exist   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    Select rows which are not present in other table

User · Answer

You should probably use NOT EXISTS for multiple columns

User · Answer

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

[sql] Select NOT IN multiple columns

Examples related to sql

Examples related to database

Examples related to multiple-columns