sql - single query to return values that are not present

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 (
'Scary Movie',
'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.



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',
'Hide and Seek'
  2    3    4    5    6    7    8  
Scary Movie
Hide and Seek

6 rows selected.


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

Need Your Help

Add Class Name and Clicking expand/Collaspe with javascript

javascript html javascript-events

I need some help with JavaScript.I want to add a click function to every node elements in my program and also want to add class name with the help of JavaScript. I mean by click function is that if i

Virtual functions empty definition in Base class

c++ inheritance

I understand that return values with co-variant types can differ in case of virtual functions. I wish to know if their is a possible way through which i can have an empty declaration in my base cla...