SQL Tuning: EXISTS Clause vs. IN

2:17 AM database, oracle No Comments

Still on the issue on Oracle performance tuning SQL Query. In the case of correlated subquery, we used to use IN and NOT IN. Eg

SELECT * FROM tabel1 a
WHERE a.kolom1 NOT IN
( SELECT b.kolom1 FROM tabel2 b)

In the same case, we can also use the EXISTS clause and NOT EXISTS. Eg

SELECT * FROM tabel1 a
WHERE a.kolom1 NOT EXISTS
( SELECT b.kolom1 FROM tabel2 b)

What is the difference of two this SQL statement? IN clause and NOT IN check if a value contained in the list (can be array, can be correlated subquery in the example above), while clause EXISTS and NOT EXISTS check only whether or not the existence of a list row. In performance, certainly much faster than IN EXISTS correlated subquery in the case.

When do we use IN? Wherever possible, use EXISTS, IN used in the case such as this example below:

SELECT * FROM tabel1 a
WHERE a.kolom1 IN (’1′, ‘2′, ‘3′)