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 (
'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'

Answers


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


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

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.