96

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?

Selim Yildiz
  • 4,978
  • 6
  • 16
  • 25
Gunjan Nigam
  • 1,203
  • 3
  • 10
  • 17
  • possible duplicate of [WHERE col1,col2 IN (...) \[SQL subquery using composite primary key\]](http://stackoverflow.com/questions/4622453/where-col1-col2-in-sql-subquery-using-composite-primary-key) – Phrogz Nov 07 '11 at 07:19
  • 3
    Your code is valid Standard Full SQl-92 syntax. You only added the 'sql' tag to your question. If you meant a particular product (e.g. SQL Server) then you should find a specific tag for it (the syntax is not supported on SQL Server, BTW). – onedaywhen Nov 07 '11 at 10:13
  • Would this happen to be OpenEdge? Unfortunately Open Edge doesn't implement the full SQL-92 specification and neither `not in` or `not exists` work, only a `left join where = null` strategy will work with OpenEdge. – Brett Ryan Aug 25 '16 at 02:03

4 Answers4

124

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.

Michał Powaga
  • 21,562
  • 8
  • 48
  • 60
  • It worked in my case. I just wanted to exclude the rows whose A and B columns existed in a Exclusion table. SQL Server 2016. – russellhoff Feb 23 '18 at 11:24
  • It works with MySQL I used it instead of EXCEPT, because MySQL does not supports EXCEPT syntax – Kamil Nękanowicz Mar 18 '19 at 15:39
  • 4
    This won't work in SQL Server if id1 or id2 include NULL values, you'll need to use the coalesce function: `select 1 from likes l where coalesce(f.id1, 0) = coalesce(l.id1, 0) and coalesce(f.id2, 0) = coalesce(l.id2, 0)` – f.cipriani Oct 17 '19 at 09:03
22

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
9

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)
vacolane
  • 141
  • 1
  • 5
  • 1
    concat is going to give false matches. (i.e. "ab", "cd" and "a", "bcd") – mrm May 21 '20 at 23:31
  • so maybe add a separator in the middle, like "ab" + "/" + "cd" – vacolane May 23 '20 at 13:22
  • 2
    You can avoid the false matches by using a separator that is guaranteed not to be used by either column, but the bigger issue is that your query requires a full table scan due to your use of WHERE CONCAT. Indexes on the first_name or last_name columns won't be useful for the query planner. – mrm May 24 '20 at 18:00
-1

You should probably use NOT EXISTS for multiple columns.

RBT
  • 21,293
  • 19
  • 144
  • 210
Raoul George
  • 2,805
  • 1
  • 20
  • 25