112

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
  • 5,254
  • 6
  • 18
  • 28
Gunjan Nigam
  • 1,363
  • 4
  • 10
  • 18
  • 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

142

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
  • 22,561
  • 8
  • 51
  • 62
  • 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. – joninx 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
  • 6
    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
23

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
  • 605,456
  • 145
  • 1,078
  • 1,228
10

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
  • 167
  • 1
  • 6
  • 4
    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
  • 4
    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
-5

You should probably use NOT EXISTS for multiple columns.

RBT
  • 24,161
  • 21
  • 159
  • 240
Raoul George
  • 2,807
  • 1
  • 21
  • 25