CODEWITHSUNDEEP
oracle-databaserowconditional-statements
metrangia
metrangia

Reputation: 137

Returning a row when the row doesn't exist in an Oracle table, using IN condition

I have an Oracle query like this:

select col1, col2
from table
where col1 in (111, 222, 333);

Let's say that 111 and 333 exist, but 222 doesn't. I want to get 3 rows back, including one for 222, even though that value doesn't exist in the table. Like this, for example:

col1 col2
---- ----
 111 aaa
 222 NoValue
 333 ccc

Of course I get just 2 rows back. How do I get a row back for every value in the in condition, even when the row doesn't exist in the table?

I was doing individual queries for every value of col1, getting back either 0 or 1 row, but that gets inefficient when there are many rows involved, which is why I want to do this in one query.

To summarize, I need to know, for every value in the in condition, (1) if a row exists (and what the value of col2 is), or (2) if the row doesn't exist. I found ways to do something similar, but not with an in condition. That's the part that has me stumped. Thanks for any suggestions.

Upvotes: 2

Views: 93

Answers (2)

mrjoltcola
mrjoltcola

Reputation: 20842

Try an outer join against a subquery. The subquery is simply a list of values converted to a collection, which can be evaluated as a table:

select b.col1, col2 
from  a
right join (
    select column_value as col1 
    from table(sys.odcinumberlist(111,222,333))
) b 
on a.col1 = b.col1
;

Upvotes: 0

Matthew McPeak
Matthew McPeak

Reputation: 17934

You need to have a row source for the list of values you want to check against. Then, LEFT JOIN your table to that row source. Like this:

WITH t AS
       (SELECT 111 col1,
               'aaa' col2
        FROM   DUAL
        UNION ALL
        SELECT 333,
               'ccc'
        FROM   DUAL),
     check_list AS
       (SELECT 111 check_value FROM DUAL
        UNION ALL
        SELECT 222 FROM DUAL
        UNION ALL
        SELECT 333 FROM DUAL)
SELECT check_list.check_value col1,
       t.col2
FROM   check_list LEFT JOIN t ON t.col1 = check_list.check_value
ORDER BY 1;

Upvotes: 2

Related Questions