6

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.)

chx
  • 11,270
  • 7
  • 55
  • 129
  • 2
    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
  • 1
    You mean like an antijoin or a `NOT IN()` clause? – Jeremy Holovacs Jul 16 '12 at 19:17
  • 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

6 Answers6

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;
  • that CTE is ansi standard syntax? Wild. – Jeremy Holovacs Jul 16 '12 at 19:17
  • @JeremyHolovacs: yes it is. Since 2003 I think –  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 ;) –  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 ...` –  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. –  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. –  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
3

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.

TelJanini
  • 835
  • 9
  • 25
  • 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` –  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
1

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
tenfour
  • 36,141
  • 15
  • 83
  • 142
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.

KeithS
  • 70,210
  • 21
  • 112
  • 164
  • 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
0

with Mysql 8 you can use JSON_TABLE and select table from JSON array of values

SELECT *
      FROM
     JSON_TABLE(
       '[1,2,3,4,5,6]',
       '$[*]' COLUMNS( id INT PATH '$' ERROR ON ERROR )
     ) as v
 left join the_real_table t on t.id = v.id
where t.id is null;
Adam Mátl
  • 116
  • 4
-3

You could use a NOT IN clause.

SELECT column FROM table WHERE column NOT IN (2,4,5)
Marcus Recck
  • 5,075
  • 2
  • 16
  • 26