Header Ads Widget

Best Explained uses of EXISTS and IN Clause in Oracle SQL - Software Queries

Hi and Welcome back ! I am going to share one more important clause from Oracle SQL. This is very important from your work point of view and any technical interview's point of view. I have been asked this question in almost every interview. Let us see what is this.

We are going to discuss about EXISTS clause and IN clause in Oracle SQL. Many time we use it but if someone ask, we do not have exact answer about its uses and applications. So we will understand it now.


Best Explained uses of EXISTS and IN Clause in Oracle SQL

What is the use of EXISTS clause in Oracle?


Exists clause is used in such scenario where we need to extract set of data from a table A and we also need to check if that set of data should exists already in some other table B. 


Now you will say, we can use IN clause for this, then why do we need EXISTS clause ?


Yes you are correct, they are used in sub queries. We can use IN clause to check data is present in the table B or not. But, then comes speed of data fetching and in real life scenarios, most of the time you need to deal with the large volume of data stored in tables.

In terms of speed, performance Exists clause is beneficial. We will see the syntax and then discuss some points. 

There is a primary difference between IN and EXISTS


  • In IN clause, first sub query runs and the output is used as input for outer query. 
  • In Exists clause, first the main query runs and take one row and check it in the sub query as input for the sub query in where clause. 


Now in Exists clause we are feeding sub query one input at a time and in IN clause we are feeding main query one or more than one input depending on sub query's output. So Exists clause is faster. 

One point you need to remember here, if data set is less then always use IN clause because it will check all at once where EXISTS clause will check one by one. 

But when Data set is bigger, use EXISTS clause always.


How do EXISTS clause work ?

It work on boolean variable. True or False. It checks if that particular exists or not and then come out from sub query with true or false rather than the value itself. 

Syntax:

SELECT * 

  FROM ADDRESS A1 

 WHERE EXISTS (SELECT 'X'

                 FROM EMPLOYEE E1

                WHERE E1.employee_id=A1.employee_id);

                                        

Above query will fetch address from the table A1 one row at a time and check in E1 table if the employee exists.

 

How do IN clause work ?

Sub query runs first in IN clause and data set from sub query is used to query outer main query to get final result.

Syntax:

SELECT * 

  FROM ADDRESS A1 

 WHERE A1.employee_id IN (SELECT employee_id

                 FROM EMPLOYEE);

                                        

In above query, sub query will fetch all employee_id from the table employee and then outer query will use that data set as input in where clause. Yes we can use where clause in sub query to limit data.


How do EXISTS clause is beneficial in above case? 


Suppose we have 4 Lakhs employee present in employee table. Sub query in IN CLAUSE will fetch all 4 lakhs employee id and then try to fetch address for them in Address table. Outer query take one input and check in Address table till the last record and this will be repeated 4 Lakhs time.

In case of EXISTS clause, the outer query will execute the select statement and store data set in an implicit cursor. Then it will start checking one record at a time if that employee id exists in the sub query table. If the process gets that employee id at some particular row, it will stop checking for that employee id and start checking for next employee id. It will not check that value in entire table every time. In this way EXISTS clause will need less number of iteration as compared to IN clause.

That is why we use EXISTS Clause when performance matters. I hope you would have understood the use and how and when we use.


One important tip: When we use EXISTS clause, you must use where clause inside sub query to join the table used in outer query. Else sub query will return something and outer query will treat as TRUE. Because of this outer query will return all data from main table.  


Related Articles:

Post a Comment

0 Comments