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

Question

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.

Tim Biegeleisen · Answer 1 · 2018-04-23T07:58:12.353

1

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)

edited Apr 23 '18 at 07:58

answered Apr 23 '18 at 07:32

Tim Biegeleisen

502,043
27
286
360

I think the array may be change – flyingfox Apr 23 '18 at 07:34
Is anti-joining better or worse performance-wise than the "WHERE NOT IN ([sub query])"? – plasmaTonic Apr 23 '18 at 07:48

score 1 · Answer 2 · answered Apr 23 '18 at 07:34

1

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
  )

answered Apr 23 '18 at 07:34

ScaisEdge

131,976
10
91
107

MT0 · Accepted Answer · 2018-04-23T08:14:03.627

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

Query 2 was closest to what I was looking for - a way to keep all the values in an array form (so it is easily extendable) — plasmaTonic, Apr 23 '18 at 14:34

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

3 Answers3