CODEWITHSUNDEEP
sqloracle-database
Chrisb
Chrisb

Reputation: 1

Oracle SQL Question, Looking for rows that don't contain specific values

I have seen answers to this that get close, but don't quite do it for me.

Imagine that you have a several strings. You want to find the rows in an existing table that contain these strings and the rows that don't.

To find the rows that do contain the strings in a column is, of course, easy, e.g. myTable has the following columns:

With the following rows:

ABC | The First Row
GHI | The Second Row
JKL | The Third Row

select * 
from myTable 
where myTableID  in ('ABC','DEF','GHI','JKL');

myTable doesn't contain a row with a value of 'DEF' in column myTableID. The answer set would correctly not contain a row for that. There isn't one!

However what I want to be able to do is treat this like a bit like like a LEFT OUTER JOIN with the value 'DEF' in front of a set of columns that contain nulls. Giving the following kind of result:

ABC| ABC | The First Row
DEF| null|null
GHI| GHI|The Second Row
JKL| JKL|The Third Row

I have several hundred key values that I need to look at.

Does anyone here have any thoughts about the best way to do this?

Upvotes: 0

Views: 865

Answers (2)

MT0
MT0

Reputation: 167774

Use a collection or a VARRAY and a table collection expression with a LEFT OUTER JOIN. SYS.ODCIVARCHAR2LIST is a built in VARRAY that you could use:

SELECT t.COLUMN_VALUE AS id,
       y.*
FROM   TABLE(
         SYS.ODCIVARCHAR2LIST( 'ABC', 'DEF', 'GHI', 'JKL' )
       ) t
       LEFT OUTER JOIN your_table y
       ON ( t.COLUMN_VALUE = y.id )

Or you can define your own collection:

CREATE TYPE stringlist IS TABLE OF VARCHAR2(20);

and use it:

SELECT t.COLUMN_VALUE AS id,
       y.*
FROM   TABLE(
         stringlist( 'ABC', 'DEF', 'GHI', 'JKL' )
       ) t
       LEFT OUTER JOIN your_table y
       ON ( t.COLUMN_VALUE = y.id )

which would output:

ID  | ID   | VALUE         
:-- | :--- | :-------------
ABC | ABC  | The First Row 
DEF | null | null          
GHI | GHI  | The Second Row
JKL | JKL  | The Third Row

db<>fiddle here

You can also pass the collection/VARRAY as a single bind variable.

Upvotes: 0

GMB
GMB

Reputation: 222402

I have several hundred key values that I need to look at.

That's a lot of values to write manually. I would suggest putting them all in a table, say in a myref(id).

You can then generate the expected resultset with just a left join:

select
    r.id,
    t.*
from myref r
left join mytable t on t.id = r.id

Upvotes: 1

Related Questions