sql - single query to return values that are not present

Question

For example, you have a simple table with just one column. ie.

CREATE TABLE movies   (title VARCHAR2(255 BYTE))

set up with the following data:

INSERT INTO movies   (title) VALUES ('Scream');
INSERT INTO movies   (title) VALUES ('Blair Witch');
INSERT INTO movies   (title) VALUES ('Friday the 13th');
INSERT INTO movies   (title) VALUES ('Scary Movie');
INSERT INTO movies   (title) VALUES ('Hide and Seek');
INSERT INTO movies   (title) VALUES ('Alien vs Predator');

Is there a single query or PL/SQL that will do the following dynamically (ie without having to manually do a "UNION select 'scream' from dual..." for every value)?

Obviously this query is wrong but you get the idea:

Select * from movies
where title in (
'Scream',
'Scary Movie',
'Exorcist',
'Dracula',
'Saw',
'Hide and Seek'
)

Desired result being a record for every value in the "WHERE TITLE IN" clause where the record is not present in the table. ie.

'Exorcist'
'Dracula'
'Saw'

Selected rows all ways come from a relation (table). You must create a table at some moment, probably with "UNION select 'scream' from dual..." for every value like you were saying. — dani herrera, Nov 04 '11 at 10:28

score 3 · Accepted Answer · edited May 23 '17 at 12:19

If you're using 10g or higher, you can build a function which converts a CSV string into a dynamic table. Check out the code for a string tokenizer in this other response.

You would use it like this:

select * from movies
where title NOT in (
         select * 
          from table (string_tokenizer
                      (
                          'Scream, Scary Movie,Exorcist,Dracula,Saw,Hide and Seek'
                        )

                  )
     )
/

Here is a slightly simpler implementation which doesn't require any additional infrastructure:

SQL> select * from table(sys.dbms_debug_vc2coll('Scream',
'Scary Movie',
'Exorcist',
'Dracula',
'Saw',
'Hide and Seek'
 ))
/
  2    3    4    5    6    7    8  
COLUMN_VALUE
--------------------------------------------------------------------------------
Scream
Scary Movie
Exorcist
Dracula
Saw
Hide and Seek

6 rows selected.

SQL>

This is similar to the Table Value Constructor, but it does only work for single column "tables".

The keyword `table` and the name `sys.dbms_debug_vc2coll` were what I was looking for. Helped me avoid repeating `union select` multiple times. Thanks! — Krzysztof Jabłoński, Sep 24 '15 at 10:50

score 2 · Answer 2 · answered Nov 04 '11 at 13:27

2

What you want is the "Table Value Constructors" feature. I don't think Oracle supports that.

See the article of Joe Celko about: Table Value Constructors in SQL Server 2008 amd an example taken from there:

SELECT *
FROM
  ( VALUES
      (101, 'Bikes'),
      (102, 'Accessories'),
      (103, 'Clothes')
  ) AS Category(CategoryID, CategoryName);

answered Nov 04 '11 at 13:27

ypercubeᵀᴹ

113,259
19
174
235

shame oracle does not have this – toop Nov 04 '11 at 13:55
I think DB2 has it, too. And SQL-Server only at latest (2008) versions. – ypercubeᵀᴹ Nov 04 '11 at 13:57
I would add that SOME versions of DB2 have this. I use v7r4 and later pretty heavily and it does support it. Some of the older machines (v5), though ... not so much. – Meower68 Dec 13 '17 at 17:40
@Meower68 not sure if that info should be an edit. Out of curiousity, does DB2 (the version you use) support this (query without SELECT): `VALUES (101, 'Bikes'), (102, 'Accessories'), (103, 'Clothes') ;` (Postgres does) – ypercubeᵀᴹ Dec 13 '17 at 17:56

winkbrace · Answer 3 · 2011-11-04T12:52:58.510

You can use a table type and the function table() to cast a list to a table.

CREATE OR REPLACE TYPE varchar_list_type as table of varchar2(100);

CREATE OR REPLACE function in_varchar_list ( p_string in varchar2 ) return varchar_list_type
as
    l_data             varchar_list_type := varchar_list_type();
    l_string           long default p_string || ',';
    l_n                number;
begin

    loop
        exit when l_string is null;

        l_data.extend;
        l_n := instr( l_string, ',' );
        l_data( l_data.count ) := substr( l_string, 1, l_n-1 );
        l_string := substr( l_string, l_n+1 );

    end loop;
    return l_data;
end;

Then use like this:

select * from TABLE(select cast(in_varchar_list('foo,bar,baz') as varchar_list_type) from dual)

Ofcourse you can use a bind variable or a normal variable instead of hardcoded string 'foo,bar,baz'.

edit: typo in the query ^_^"

sql - single query to return values that are not present

3 Answers3

Linked