CODEWITHSUNDEEP
sqloracle11g
kiranbirajdar
kiranbirajdar

Reputation: 305

how to display all values which don't match in in clause?

For Ex:

MY_TABLE
COL1->'P1','P2','P3'

OUTPUT SHOULD DISPLAY P4,P5

I tried:

select col1 from my_table where col1 not in ('p4','p5');

Upvotes: 1

Views: 49

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

Ideally, you should have that range of values in a proper SQL table. Lacking this, you could use an on-the-fly CTE to hold the range:

WITH cte AS (
    SELECT 'p1' AS col1 FROM dual UNION ALL
    SELECT 'p2' FROM dual UNION ALL
    SELECT 'p3' FROM dual UNION ALL
    SELECT 'p4' FROM dual UNION ALL
    SELECT 'p5' FROM dual
)
SELECT t1.col1
FROM cte t1
LEFT JOIN my_table t2
    ON t1.col1 = t2.col1
WHERE t2.col1 IS NULL

Upvotes: 1

Related Questions