Using a subquery for IN clause Oracle

Question

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
...

G.Arima · Accepted Answer · 2018-06-12 10:12:30Z

2

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)
...

answered Jun 12, 2018 at 10:12

G.Arima

1,1711 gold badge6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank Schmitt · Accepted Answer · 2018-06-12 14:27:02Z

1

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).

edited Jun 12, 2018 at 14:27

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

answered Jun 12, 2018 at 10:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Frank Schmitt Over a year ago

Is it possible that you've got a copy/paste error? The duplicate SELECT 1 FROM looks suspicious.

Frank Schmitt Over a year ago

You're welcome. I've removed the duplicate FROM, as well.

Barbaros Özhan · Accepted Answer · 2018-06-12 11:17:30Z

0

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;

edited Jun 12, 2018 at 11:17

answered Jun 12, 2018 at 10:13

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

Using a subquery for IN clause Oracle

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related