Sql Exclusion Query

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 pho

Solution 1:

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

SELECT level -1+8001231000FROM dual
CONNECTBY 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
 CONNECTBY level <=8001239999-8001231000+1
)
SELECT*FROM all_numbers a
 WHERENOTEXISTS(
    SELECT1FROM phone_numbers p
     WHERE a.phone_number = p.phone_number)

Solution 2:

If your phone numbers are character:

select*from mytable
where phone_number notbetween'8001231000'and'8001239999'

or if they are numeric:

select*from mytable
where phone_number notbetween8001231000and8001239999

Solution 3:

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;

Solution 4:

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

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