1

I have a list, for instance: 1,2,5,6,8,12,15

I'm trying to come up with an SQL query that returns to me a list of numbers, from the previous list, not present in another list.

So, suppose I'm getting all id's from a table and those are: 1,3,7,8,15

The resultset should be: 2,5,6,12

Because those were the numbers not present in the second list, but present in the first.

I thought this one would be easy, but I'm stumped. Googling it has yielded no results I can use, just listing things about lists and left joins.

KdgDev
  • 14,299
  • 46
  • 120
  • 156
  • here is a related question: http://stackoverflow.com/questions/7125291/postgresql-not-in-versus-except-performance-difference-edited-2 – Andreas Dec 29 '15 at 19:16

4 Answers4

3
with a (id) as (values
    (1),(2),(5),(6),(8),(12),(15)
), b (id) as (values
    (1),(3),(7),(8),(15)
)
select id from a
except all
select id from b
;
 id 
----
  6
  5
 12
  2

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-EXCEPT

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
2

I would recommend using an inner join and checking for nulls.

with a (id) as (values
    (1),(2),(5),(6),(8),(12),(15)
), b (id) as (values
    (1),(3),(7),(8),(15)
)
select a.id from a
left join b on a.id=b.id
where b.id is null;
Andreas
  • 4,937
  • 2
  • 25
  • 35
1

You can use the NOT IN statement to get what you need:

SELECT
    my_id
FROM
    My_Table
WHERE
    my_id NOT IN (SELECT other_ids FROM Some_Other_Table)
Tom H
  • 46,766
  • 14
  • 87
  • 128
1

An anti-join is a very efficient construct:

select a.id
from a
where not exists (
  select null
  from b
  where a.id = b.id
)
Hambone
  • 15,600
  • 8
  • 46
  • 69