Gavin Osborn Gavin Osborn - 4 months ago 15
SQL Question

Writing a SQL query over a given set of values

I am trying to write a plsql query that allows me to query a set of known values that are not stored in a table.
Say those known values are the following strings:


  • abc

  • def

  • ghi

  • jkl



I would like to achieve something like the following:



select * from [fill-in-the-blank] myvalues
where not myvalues in
(
select id from dbtable
)



..where I am trying to ascertain which of those know values are not in a database table.

Constraints


  • This is pl/sql (oracle)

  • This solution must run from within Oracle PL/SQL Developer

  • I only have read access to the schema so I cannot create temporary tables.



Any ideas?

Answer

You could use a Common Table Expression (CTE) to accomplish this:

with cte as (
    select 'abc' as id from dual
    union all
    select 'def' from dual
    union all
    select 'ghi' from dual
    union all
    select 'jkl' from dual
)
select * 
from cte
where not id in 
(
    select id from dbtable
)

In fact, you may not even really need the CTE at all (though I find it aids readability):

select * 
from (
    select 'abc' as id from dual
    union all
    select 'def' from dual
    union all
    select 'ghi' from dual
    union all
    select 'jkl' from dual
)
where not id in 
(
    select id from dbtable
)
Comments