Re: SQL query for finding values that are not contained in a table

From: <fitzjarrell_at_cox.net>
Date: 20 Oct 2005 13:34:46 -0700
Message-ID: <1129840486.054866.190680@g14g2000cwa.googlegroups.com>

dimitris_at_cs.umb.edu wrote:
> Thanks Jens, It actually works. Nice trick :)
> I wished that sql treated expression lists
> like subqueries in order to make queries
> like this simpler.
>
> E.g if we could say something like
>
> select * from ('a1', 'a2', ....)
> MINUS
> select col1 from tab1
>
> D.

This could be a possiblity:

select col1
from
(select decode(rownum, 1, 'a01',2, 'a02',3, 'a03',4, 'a04',5, 'a05',6,

'a06',7,
'a07',8, 'a08',9, 'a09',10, 'a10',11, 'a11',12, 'a12',13, 'a13',14,
'a14',15, 'a

15',16, 'a16',17, 'a17',18, 'a18') col1 from all_objects where rownum < 19)
minus
select col1
from listtest
order by 1
/

It isn't pretty, but it does work:

SQL> select * From listtest;

COL1

---

a01
a03
a04
a05
a08
a10
a17

7 rows selected.

SQL> set echo on
SQL> @tabtest
SQL> select col1

  2 from
  3 (select decode(rownum, 1, 'a01',2, 'a02',3, 'a03',4, 'a04',5,

'a05',6, 'a06
',7, 'a07',8, 'a08',9, 'a09',10, 'a10',11, 'a11',12, 'a12',13,
'a13',14, 'a14',1

5, 'a15',16, 'a16',17, 'a17',18, 'a18') col1 from all_objects where rownum < 19)
  4 minus
  5 select col1
  6 from listtest
  7 order by 1
  8 /

COL1

---

a02
a06
a07
a09
a11
a12
a13
a14
a15
a16
a18

11 rows selected.

SQL> David Fitzjarrell Received on Thu Oct 20 2005 - 15:34:46 CDT