CODEWITHSUNDEEP
sqloracle-database
Felipe Vidal
Felipe Vidal

Reputation: 505

IN clause for multiple columns *and* values

I'm trying to save time at using the "IN" clause.

In a file that has the same values in several columns, what I would normally do is:

SELECT * FROM TABLE_1
WHERE COLUMN_1 IN (VALUE_1, VALUE_2, VALUE_3, ...)
AND COLUMN_2 IN (VALUE_1, VALUE_2, VALUE_3, ...)
AND COLUMN_3 IN (VALUE_1, VALUE_2, VALUE_3, ...);

Is there a IN-like clause that can achieve the same with only one equivalence?

E.g.

SELECT * FROM TABLE_1
WHERE (COLUMN_1, COLUMN_2, COLUMN_3) IN (VALUE_1, VALUE_2, VALUE_3, ...);

Upvotes: 2

Views: 116

Answers (1)

Popeye
Popeye

Reputation: 35930

You can avoid repeating the values using the following technique:

WITH DATAA ( INPUTS ) AS (
    SELECT
        'VALUE_1, VALUE_2, VALUE_3, ...'
    FROM
        DUAL
)
SELECT
    *
FROM
    TABLE_1 T1
    JOIN (
        SELECT
            REGEXP_SUBSTR(DATAA.INPUTS, '[^,]+', 1, LEVEL) ALL_VALUES
        FROM
            DATAA
        CONNECT BY
            REGEXP_SUBSTR(DATAA.INPUTS, '[^,]+', 1, LEVEL) IS NOT NULL
    ) T2 ON T1.COLUMN_1 IN (T2.ALL_VALUES)
            AND T1.COLUMN_2 IN (T2.ALL_VALUES)
            AND T1.COLUMN_3 IN (T2.ALL_VALUES);

Cheers!!

Upvotes: 1

Related Questions