Which values are missing in SQL from a list?

Question

I have a list of values, say 1,2,3,4,5,6. I have a SQL table with a field storing these values but only three rows, say, 2,4,5. What sort of query will return three rows 1,3,6 for me? (storing all six in another table and left join'ing is cheating.)

Storing all the values in a lookup table and joining is by far the best way to do this. — HLGEM, Jul 16 '12 at 19:14
Please include the type of datasbase you are on, the table structure and the SQL query that is returning three rows. — John D, Jul 16 '12 at 19:15
the answer that worked for me is here: http://stackoverflow.com/questions/10013475/select-that-returns-list-of-values-not-occurring-in-any-row#answer-27053607 — knocte, Dec 18 '15 at 05:08

a_horse_with_no_name · Answer 1 · 2012-07-17T06:49:15.650

8

You didn't state your DBMS, so this is the ANSI SQL Version:

with the_values (id) as ( 
  values (1),(2),(3),(4),(5),(6)
)
select v.id 
from the_values v
  left join the_real_table t on t.id = v.id
where t.id is null;

edited Jul 17 '12 at 06:49

answered Jul 16 '12 at 19:16

a_horse_with_no_name

497,550
91
775
843

that CTE is ansi standard syntax? Wild. – Jeremy Holovacs Jul 16 '12 at 19:17
@JeremyHolovacs: yes it is. Since 2003 I think – a_horse_with_no_name Jul 16 '12 at 19:18
Op said left joining is "cheating" whatever that means. Didn't know we were taking an exam. ;) – sam yi Jul 16 '12 at 19:22
@samyi: actually it says "*storing in a temp table **and** left joining*" is cheating. But I'm not storing them in a temp table so the boolean expression is still true ;) – a_horse_with_no_name Jul 16 '12 at 19:24
The syntax doesn't work in SQL Server 2008, but something similar does. – Gordon Linoff Jul 16 '12 at 19:27
@GordonLinoff: yes SQL Server does not support the `values` row constructor. You would need to use a `SELECT 1 UNION ALL SELECT 2 ...` – a_horse_with_no_name Jul 16 '12 at 19:30
1

I am a little torn on which answer to accept -- this is ANSI but doesn't work with MySQL and to get it working on SQL Server you need the `SELECT - UNION` construct and if you write that out then it's easier to `JOIN` on that construct without using `WITH`. Sounds like `WITH` is SQL'99 btw http://stackoverflow.com/questions/324935/mysql-with-clause – chx Jul 16 '12 at 22:49
@chx: the syntax works in PostgreSQL, HSQLDB, Teradata, Vertica and DB2. You should have mentioned that you are limited by MySQL in your question. – a_horse_with_no_name Jul 17 '12 at 06:46
Sorry about that -- I need a solution that works on MySQL *and* a wide range of databases. – chx Jul 17 '12 at 08:07
@chx: the accepted solution won't work on all databases either (e.g. Oracle, DB2, Firebird) as they don't allow a SELECT without a table. – a_horse_with_no_name Jul 17 '12 at 08:20
Works on Postgres – yǝsʞǝla Feb 13 '20 at 03:13
works flawlessly! – Gaurav Mar 30 '22 at 19:13

score 4 · Accepted Answer · answered Jul 16 '12 at 19:39

4

You could also try using EXCEPT (similar to MINUS in Oracle):

(SELECT 1
UNION
SELECT 2
UNION 
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6)
EXCEPT
(SELECT 2
 UNION
 SELECT 3
 UNION
 SELECT 4)

Or, more relevant to your example:

(SELECT 1
UNION
SELECT 2
UNION 
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6)
EXCEPT
(SELECT Field FROM Table)

where Field contains 2, 4, and 5.

answered Jul 16 '12 at 19:39

TelJanini

817
9
23

That's a great idea. Here's a slight modification, one that works even in MySQL: `SELECT missing.1 AS missing FROM test RIGHT JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) x ON x.1 = test.foo WHERE foo IS NULL;` – chx Jul 16 '12 at 22:46
Nice solution. For databases that support the `values` row constructor this could be shortened to `values (1),(2),(3),(4),(5),(6) EXCEPT SELECT col FROM foo` – a_horse_with_no_name Jul 17 '12 at 06:52
You mention that my query won't work on Oracle/DB2/Firebird -- would `SELECT missing.1 AS missing FROM test RIGHT JOIN (values (1),(2),(3),(4),(5),(6))...` work there? – chx Jul 17 '12 at 08:33
@chx that doesn't work in MySQL, it gives `Unknown column 'missing.1' in 'field list'` – knocte Dec 18 '15 at 04:53
this works for MySQL: http://stackoverflow.com/questions/10013475/select-that-returns-list-of-values-not-occurring-in-any-row#answer-27053607 – knocte Dec 18 '15 at 06:42

tenfour · Answer 3 · 2012-07-16T19:21:28.257

The not in operator will do the check you want.

declare @allValues table (value int)
insert @allValues values (1)
insert @allValues values (2)
insert @allValues values (3)
insert @allValues values (4)
insert @allValues values (5)
insert @allValues values (6)

declare @someValues table (value int)
insert @someValues values (2)
insert @someValues values (4)
insert @someValues values (5)

select
    *
from
    @allValues
where
    value not in (select value from @someValues)

Another method which is probably faster, using joins:

select
    av.value
from
    @allValues av
    left join @someValues sv on (av.value = sv.value)
where
    sv.value is null

score 0 · Answer 4 · answered Jul 16 '12 at 19:16

0

It's not cheating if it does the job. Set up a temp table or table variable with all the possible rows, then use a WHERE NOT EXISTS or a LEFT OUTER JOIN combined with WHERE TableVariableID IS NULL.

answered Jul 16 '12 at 19:16

KeithS

68,043
20
107
163

It doesn't do the job for the simple reason that I don't have permissions to create a temporary table... What I have is basic select access to a very large table and an arbitrary list of a couple hundred external IDs and I need to figure out which ones don't have any entries in that table. (The list is coming from a completely external source -- if they don't exist in that table they don't exist at all in the db.) I ended up selecting out the ones that did exist and then using Excel and a tedious manual process. – user3067860 Jun 01 '22 at 14:27

score -3 · Answer 5 · answered Jul 16 '12 at 19:15

-3

You could use a NOT IN clause.

SELECT column FROM table WHERE column NOT IN (2,4,5)

answered Jul 16 '12 at 19:15

Marcus Recck

5,015
2
15
26

Which values are missing in SQL from a list?

5 Answers5