SQL Exclusion Query

Question

Is it possible in a single SQL statement to do the following:

Use a subset of telephone numbers in a prompt, for example 8001231000-8001239999. Then query my database that has phone numbers in it, and return which phone numbers in the original subset are NOT in my database? My db is Oracle 10g.

Basically instead of bringing back which phone numbers ARE between 8001231000-8001239999, I want to know which phone numbers between 8001231000-8001239999 are NOT in my database.

Answer 1

You are looking for the "NOT IN" Operator with a subquery matching those telephone numbers.

Answer 2

Assuming that the phone number is a NUMBER, you can generate the list of all phone numbers in a particular range

 SELECT level - 1 + 8001231000
   FROM dual
CONNECT BY level <= 8001239999-8001231000+1

You can then join this list of all the phone numbers in the range to your actual table of phone numbers. Something like

WITH all_numbers AS (
  SELECT level - 1 + 8001231000 phone_number
    FROM dual
 CONNECT BY level <= 8001239999-8001231000+1
)
SELECT *
  FROM all_numbers a
 WHERE NOT EXISTS(
    SELECT 1
      FROM phone_numbers p
     WHERE a.phone_number = p.phone_number)

Answer 3

Something like this:

SELECT numbers_array.num
  FROM (SELECT 8001231000 + LEVEL num 
          FROM dual 
       CONNECT BY LEVEL <= (8001239999 - 8001231000)
       ) numbers_array
 WHERE numbers_array.num NOT IN (SELECT number_you_have FROM your_table_of_numbers)

Create a pseudo-list of all possible numbers and exclude existing numbers from it.

Answer 4

If your phone numbers are character:

select * from mytable
where phone_number not between '8001231000' and '8001239999'

or if they are numeric:

select * from mytable
where phone_number not between 8001231000 and 8001239999

Answer 5

I can't think of a way to do it with a single select, but you can do it with a single transaction. Specifically:

• Create a temp table with the values in your range (should be possible with a single create table and one insert)
• DELETE the values from your temp table that do exist in your main table
• SELECT the values from the temp table that are left

Edit: Bill Karwin's answer is better. Same concept with the temp table, but then a single select to pull out the values that don't exist.

Answer 6

I would load a temporary table with all 10000 phone numbers in the range you want to check, and do an exclusion join:

SELECT a.phone_number
FROM phone_numbers_i_want_to_check AS a
LEFT OUTER JOIN phone_numbers AS b
  ON a.phone_number = b.phone_number
WHERE b.phone_number IS NULL;