CODEWITHSUNDEEP
sqloracle-database
Ravi Teja Bodla
Ravi Teja Bodla

Reputation: 21

SELECT that returns list of values not present in the table

query: 

select id from users where id in (1,2,3,4,5)

If the users table contains ids 1, 2, 3, this would return 1, 2, and 3. I want a query that would return 4 and 5. In other words, I don't want the query to return any rows that exist in the table, I want to give it a list of numbers and get the values from that list that don't appear in the table.

Upvotes: 2

Views: 1384

Answers (2)

Mark Adelsberger
Mark Adelsberger

Reputation: 45819

First you need to represent your list of numbers as a relation (probably not exactly the term Oracle docs would use, but...)

VALUES 1,2,3,4,5

Then you could use this in a query in a number of ways

SELECT *
 FROM (VALUES 1,2,3,4,5) as V(A)
WHERE A NOT IN ( -- query your table here
                   )

or

SELECT *
  FROM (VALUES 1,2,3,4,5) as V(A)
     LEFT JOIN -- your table
        ON V.A = table.column
 WHERE table.column is NULL

or 

VALUES 1,2,3,4,5
 MINUS
 -- query your table

I'm not at a computer with ORacle installed, so any of these may be a bit off on syntax, but the ideas are there...

Upvotes: 1

Austin Yi
Austin Yi

Reputation: 81

You would have to use the MINUS operator. You can also use this along with odcinumberlist without having to store your values in a temporary table

SELECT column_value
FROM table(sys.odcinumberlist(1,2,3,4,5)) 
MINUS
SELECT ids
FROM users;

Upvotes: 2

Related Questions