CODEWITHSUNDEEP
sqloracle-databasein-clause
Gary
Gary

Reputation: 129

Using a subquery for IN clause Oracle

I am having some trouble using a subquery for the IN clause of a query. Hard-coding the IN .. values allows the query to execute quickly, but using a subquery slows everything down. Is there a way to speed this query up?

SELECT col1, col2, col3 FROM table1
WHERE ...
and col1 in (SELECT col1 FROM table2)
...

*The values for the IN clause will be a list of strings

SELECT col1, col2, col3 FROM table1
WHERE ...
and col1 in ('str1', 'str2', 'str3', ...)
...

The above works fine.

EDIT: I think I was oversimplifying the problem. The query I am trying to execute looks like this:

SELECT col1, col2, col3, ...
FROM table1 t1, table2 t2
WHERE t1.col1 IN (SELECT col FROM table3)
and t1.col2 < 50
and t2.col3 = t1.col3
...

Upvotes: 0

Views: 11571

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax. You should write the query as:

SELECT col1, col2, col3, ...
FROM table1 t1 JOIN
     table2 t2
     ON t2.col3 = t1.col3
WHERE t1.col1 IN (SELECT col FROM table3) AND
      t1.col2 < 50;

I would write this using EXISTS, rather than IN:

SELECT col1, col2, col3, ...
FROM table1 t1 JOIN
     table2 t2
     ON t2.col3 = t1.col3
WHERE EXISTS (SELECT 1 FROM table3 t3 WHERE t1.col1 = t3.col) AND
      t1.col2 < 50;

The filtering is all on table1; however, the columns are being compared with inequalities. I would try the following indexes: table2(col3), table1(col2, col1), and table3(col).

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65218

Use JOIN instead,

and keep an index defined on table1.col1 or table2.col3 or table1.col3 or table3.col :

SELECT col1, col2, col3, ...
  FROM table1 t1
 INNER JOIN table2 t2 on ( t2.col3 = t1.col3 )
 INNER JOIN table3 t3 on ( t1.col1 = t3.col )
 WHERE t1.col2 < 50;

Upvotes: 0

G.Arima
G.Arima

Reputation: 1171

You cant write select * from . If you give select * from, it doesnot understand which column to compare with from table2. Use the column name you need. 

SELECT * FROM table1
WHERE ...
and col1 in (SELECT col1 FROM table2)
...

Upvotes: 2

Related Questions