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

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 no开发者_开发知识库t 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.

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

• Info Center | fullselect
• Assigning column names to a values statement

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.

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

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

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

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.