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
  • 13,833
  • 45
  • 117
  • 153
  • 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

2
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
  • 108,856
  • 25
  • 211
  • 247
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,650
  • 2
  • 23
  • 31
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
  • 45,807
  • 14
  • 84
  • 124
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
  • 14,235
  • 7
  • 44
  • 64