CODEWITHSUNDEEP
sqloracle-database
plasmaTonic
plasmaTonic

Reputation: 345

Select ids from array of numbers that are not in a Table

Given the following table data:

Students.id
-----------
1
2
3

If I want to select ids from a table that are not in an array of numbers, I can do the following:

SELECT id FROM Students
WHERE id NOT IN (1, 3, 5);

Result: (2)

But I want to do the opposite - select numbers from an array that are not in a table. How can I do that?

Here have been my attempts so far:

Attempt 1:

SELECT id FROM TABLE(1, 3, 5)
WHERE id NOT IN (SELECT id FROM Students);

RESULT: ORA-00907: missing right parenthesis
------------------------
Attempt 2:

SELECT (1, 3, 5) FROM dual
WHERE ??? NOT IN (SELECT id from Students); -- not sure what the column name should be

RESULT: Executing the first line alone gives the error ORA-00907: missing right parenthesis
--------------
Attempt 3:

SELECT TABLE(1,3,5) AS ids FROM dual
WHERE ids NOT IN (SELECT id FROM Students);

RESULT: ORA-00936: missing expression

Desired results (from my examples): (5)

Note: I found this related question, but alas it is also unanswered. Like Fuzz, my array of numbers are not stored in a table.

Upvotes: 0

Views: 2099

Answers (3)

MT0
MT0

Reputation: 168351

You can create a collection type:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TYPE NumbersList IS TABLE OF NUMBER;

and then you can use it in a query:

Query 1:

SELECT id
FROM   students
WHERE  id NOT MEMBER OF NumbersList( 1, 3, 5 )

Results:

| ID |
|----|
|  2 |

Or, to reverse it and have the numbers in the collection not in the table, you can use a table collection expression (TABLE(:your_collection)):

Query 2:

SELECT COLUMN_VALUE
FROM   TABLE( NumbersList( 1, 3, 5 ) )
WHERE  COLUMN_VALUE NOT IN ( SELECT id FROM students )

Results:

| COLUMN_VALUE |
|--------------|
|            5 |

You can even pass the collection as a bind variable (examples passing it from PL/SQL and from a java array).

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522181

You may try anti joining a CTE containing your ID numbers to the Students table:

WITH cte AS (
    SELECT 1 AS id FROM dual UNION ALL
    SELECT 3 FROM dual UNION ALL
    SELECT 5 FROM dual
)

SELECT t1.id
FROM cte t1
LEFT JOIN Students t2
    ON t1.id = t2.id
WHERE t2.id IS NULL

Your requirement is fairly easy to come by, using a join or a few other methods, provided that your data is in a proper table. If you have a long term need for this, then I recommend getting your data into tables.

We could also write the above query using EXISTS, which might have better performance:

SELECT t1.id
FROM cte t1
WHERE NOT EXISTS (SELECT 1 FROM Students t2 WHERE t1.id = t2.id)

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

You could use the table build in proper way eg: using union instead of an array :

  select id from (
  select 1 id  from dual 
  union 
  select 3  from dual 
  union 
  select 5  from dual ) t 
  where t.id NOT IN (
  SELECT id FROM Student
  )

Upvotes: 1

Related Questions