IN verses Exists

Which is better IN or Exists in Oracle

which_is_better_in_or_exists_in_oracle

The IN and EXISTS are processed very very differently.

SELECT * FROM tbl1 WHERE a IN ( SELECT b FROM tbl2 );

is typically processed as:

SELECT * 
FROM tbl1, ( SELECT DISTINCT b FROM tbl2 ) tbl2
WHERE tbl1.a = tbl2.b;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table -- typically.

Where as

SELECT * FROM tbl1 WHERE EXISTS ( SELECT NULL FROM tbl2 WHERE b = a )

is processed more like:

FOR x IN ( SELECT * FROM tbl1 )
    LOOP
        IF ( EXISTS ( SELECT NULL FROM tbl2 WHERE b = x.a )
        THEN 
            OUTPUT THE RECORD
        END IF
    END LOOP;

It always results in a full scan of TBL1 whereas the first query can make use of an index on TBL1(a).

Lets say the result of the subquery

( SELECT b FROM tbl2  )

is "huge" and takes a long time. But the table TBL1 is relatively small and executing

( SELECT NULL FROM tbl2 WHERE b = x.a )
is very very fast (nice index on tbl2(b)), then the EXISTS will be faster as the time to full scan TBL1 and do the index probe into TBL2 could be less than the time to simply full scan TBL2 to build the subquery we need to distinct on.

Lets say the result of the subquery is small -- then IN is typicaly more appropriate.

If both the subquery and the outer table are huge -- either might work as well as the other -- depends on the indexes and other factors.

See also NOT IN vs NOT EXISTS


If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.