SQL - How can I return the IDs from a where clause list that are not in the table?

Question

Given a query like this:

select customerId
  from customer
 where customerId in (
          1, 2, 3
       )

I have hundreds of IDs in the where clause's list; how would I return the IDs from the list in the where clause that are not in the table?

This is a production table that I can only run select queries against. I can only run select queries; I don't have permissions to create any tables.

Why do you have hundreds of CustomerID in your application that don't exist in the database? — Ronnis, Feb 04 '11 at 21:42
@Ronnis - I am answering a system owner's request for an ad hoc report. — Abboq, Feb 04 '11 at 21:44
@abboq - answer updated to address solving this with SELECT-only access — RichardTheKiwi, Feb 04 '11 at 21:55
@abboq, the point I was trying to make was this: Even though the CustomerID are "just numbers", they are special in the sense that they represent actual customers. So you must have gotten the customers numbers from somewhere. I bet that the list of customerID you have is the result of another SQL Query. I also think that what you are trying to achieve could be done with the original customer table and the query you got all the ID from. — Ronnis, Feb 04 '11 at 22:15
@Ronnis - The given query uses a customer entity, but my use case is a different type of business entity. I was given a list of IDs and told to generate a specific report. In this case, when the total number of rows was 3 short out of 500, I realized something unexpected had taken place. The table's rows are populated by a manual process: a human being with a bar code scanner. The three rogue IDs turned up in the first place, because HQ received physical boxes with bar codes that were accidentally never scanned (thus never inserted). — Abboq, Feb 04 '11 at 22:21
@abboq, ok fair requirement. In that case, I would just perform the query as you wrote it, and match the returned CustomerIDs against my original list. The intersection are valid CustomerIDs. The rest are rogues. — Ronnis, Feb 04 '11 at 22:35

score 6 · Answer 1 · answered Feb 05 '11 at 01:34

You can use the VALUES statement to fake up a table in your SELECT:

SELECT t1.customerId
FROM
  (VALUES (1), (2), (3), (4)) AS t1(customerId)
LEFT OUTER JOIN
  customer c
ON
  c.customerId = t1.customerId
AND
  c.customerId IS NULL

References

score 4 · Answer 2 · answered Feb 05 '11 at 00:35

There is a much easier way to do this on DB2, using VALUES to construct a table on the fly:

select 
   customerID
from 
   table(values (1),(2),(3)) as a (customerID)  
except 
select 
   customerID 
from 
   customers;

Note that you need to put parenthesis around each value to ensure that each value is a "row" in the table construct.

For this example, if your customers table has customerIDs 1, 2, 3 and 4, this query will return the value 4.

This should work on DB2 UDB for z/OS Version 8 and newer. It will aslo work on DB2 for Linux/UNIX/Windows.

Joe Stefanelli · Answer 3 · 2011-02-04T21:47:08.457

3

I'd put those IDs into a table structure and left join that against your customer table.

select t.customerId
    from SomeTable t
        left join customer c
            on t.customerId = c.customerId
    where c.customerId is null

edited Feb 04 '11 at 21:47

answered Feb 04 '11 at 21:39

Joe Stefanelli

132,803
19
237
235

1

OP can only run SELECT queries – RichardTheKiwi Feb 04 '11 at 21:59

RichardTheKiwi · Accepted Answer · 2011-02-04T22:01:18.580

The really, really brutal approach, for most DBMS, this will work (except Oracle where you need to select..from dual). This should work on DB2 even if you have no access to create/update tables and can only SELECT

select N1.N * 1000 + N2.N * 100 + N3.N * 10 + N4.N as NonExistentCustomerID
from (
    select 1 as N union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9 union all
    select 0) N1
cross join (
    select 1 as N union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9 union all
    select 0) N2
cross join (
    select 1 as N union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9 union all
    select 0) N3
cross join (
    select 1 as N union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9 union all
    select 0) N4
where N1.N * 1000 + N2.N * 100 + N3.N * 10 + N4.N in (1,2,3)
    and not exists (
        select * from customer c where c.customerid = v.number + v2.number*1000)

Expand the subqueries as required to cover your full number range.

If you could create tables (or had one handy), create a "numbers" table instead with the digits 0 to 9 (10 records), and keep joining to itself, i.e.

create table Numbers (N int)
insert into Numbers
select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all
select 7 union all select 8 union all select 9 union all
select 0

select N1.N * 1000 + N2.N * 100 + N3.N * 10 + N4.N as NonExistentCustomerID
from numbers N1
cross join numbers N2
cross join numbers N3
cross join numbers N4
where N1.N * 1000 + N2.N * 100 + N3.N * 10 + N4.N in (1,2,3)
    and not exists (
        select * from customer c where c.customerid = v.number + v2.number*1000)

A numbers table is always useful for various queries.

For reference for SQL Server, assuming the numbers are within the range 0-2047, you can use

select v.number
from master..spt_values v
left join customer c on c.customerid = v.number
where v.type='P' and v.number in (1,2,3)
  and v.customerid is null

If you need a larger range, keep joining to master..spt_values again to get a larger range

select v.number + v2.number*1000 as NonExistentCustomerID
from master..spt_values v
inner join master..spt_values v2 on v2.type='P'
where v.type='P' and v.number + v2.number*1000 in (1,2,3)
  and v.number between 0 and 999
  and not exists (
    select * from customer c where c.customerid = v.number + v2.number*1000)
order by 1

This solution is insanity. See below for a solution using VALUES. — Ian Bjorhovde, Feb 05 '11 at 00:35

score 1 · Answer 5 · answered Feb 04 '11 at 21:38

1

select customerId
  from customer
EXCEPT
select customerId
  from customer
 where customerId in (
          1, 2, 3
       )

answered Feb 04 '11 at 21:38

Saggio

2,212
6
33
50

score 1 · Answer 6 · answered Feb 04 '11 at 21:41

One easy way is to use a tally table, let's say it's called numbers with column number - of all numbers (this code may not be the best, but it should be clear what the intent is):

select number
from numbers
where number in (1, 2, 3)
and number NOT IN (select customerId from customer)

Another possibility is to write the ids you are looking for into an actual table or table variable.

SQL - How can I return the IDs from a where clause list that are not in the table?

6 Answers6

Linked