CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Dcarr
Dcarr

Reputation: 55

Select IN from multiple columns

So I have a table, lets call it MAIN table, it has the following example columns

Name,
Code_1,
Code_2,
Code_3,
Code_4,
Code_5

(in my real example there's 25 Code columns)

I have a set of 300 codes that I inserted into a temporary table, what would be the best way to get the rows from the MAIN table where it matches a code from the temporary table?

Here's what I have so far that works, but it seems extremely inefficient 

SELECT * FROM MAIN WHERE (CODE_1 IN (SELECT CODE FROM TMP_TABLE) 
OR CODE_2 IN(SELECT CODE FROM TMP_TABLE) 
OR CODE_3 IN (SELECT CODE FROM TMP_TABLE)
OR CODE_4 IN (SELECT CODE FROM TMP_TABLE)
OR CODE_5 IN (SELECT CODE FROM TMP_TABLE))

Upvotes: 1

Views: 87

Answers (2)

Raunak Thomas
Raunak Thomas

Reputation: 1403

A join would be faster

SELECT * FROM MAIN 
inner join TMP_TABLE
on main.code_1 = tmp_table.code 
    or main.code_2 = tmp_table.code 
    or main.code_3 = tmp_table.code
    or main.code_4 = tmp_table.code
    or main.code_5 = tmp_table.code

But as mentioned in the comment, the join could potentially increase the number of rows if in the main table multiple code_## match the join criteria in the tmp_table

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726569

One approach would be to use a correlated subquery:

SELECT *
FROM MAIN m
WHERE EXISTS (
    SELECT *
    FROM TMP_TABLE t
    WHERE t.CODE = m.CODE_1 OR t.CODE = m.CODE_2 OR ...
)

Upvotes: 4

Related Questions