EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL exists
 

PL/SQL exists

Updated April 5, 2023

PL/SQL exists

 

 

Introduction to PL/SQL exists

PL/SQL exists is the statement in PL/ SQL which is used for checking whether any of the rows exist in the supplied parameters. This clause is most often used in the subqueries inside the where clause. If the subquery returns even a single row then the execution of the inner query stops there itself and the existing statement returns true. In case if till the last row of the table of the subquery is scanned for its execution still there is no row retrieved till the end then the exists function returns false.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

In this article, we will study the syntax of the exist statement and will also look at the implementation of the existing function inside the subqueries of where clause with the help of examples. Further, we will also throw a little light on what is the difference between the exist function and the in a clause in PL/ SQL.

Syntax:

The exists function is mostly used inside the where clause by specifying a subquery inside the parameter of the exists function for checking the existence of any row returned by the subquery. The syntax of the PL/ SQL exists function is as shown below –

SELECT *
FROM name of the table
WHERE EXISTS (subquery);

The terminologies that are used in the specified syntax are described one by one in the upcoming section –

  • Name of table – This is the table name from which we might want to retrieve the result of the final query provided if there is any existence of the row returned by the subquery.
  • Subquery – This is a select query statement that will involve retrieving some value from a particular table. This can also be a function call of a procedure call that returns the particular values. The returning result set of this subquery becomes the deciding factor about the Boolean value to be returned by the exist function.
  • EXISTS – This function checks if there is any value or row returned from the subquery and if at all while scanning the rows by the inner subquery even if a single row is returned, the exists function stops the further execution of the subquery and returns the TRUE Boolean value. If while retrieving the rows from the subquery, not even a single row is retrieved till the end then the EXISTS function returns the FALSE Boolean value.
  • Return value – The EXISTS function returns a Boolean value of there is existence of any row inside the subquery specified in the parameter.

Examples of PL/SQL exists

Let us look at one example which will help us demonstrate the implementation and usage of the EXISTS function in PL/ SQL. We have two tables named employee_details and contact_details. The contents of each of the tables are as shown by the output of below two queries –

SELECT * FROM [contact_details];

The output of the execution of the above query is –

PL SQL exists 1

SELECT * FROM [employee_details];

The output of the execution of the above query is –

PL SQL exists 2

Now, we will try to retrieve only those employees who have their contact-related details present in the contact_details table. For this, we will make the use of EXIST function in PL/ SQL and our query statement will look somewhat like the below one –

SELECT
*
FROM
employee_details e
WHERE
EXISTS (
SELECT
1
FROM
contact_details
WHERE
employee_id = e.employee_id
);

PL SQL exists 3

EXISTS versus IN –

Basically, the functionality of both functions is quite similar. The EXISTS function checks if any row is returned by subquery while IN function scans all the values specified in its parameter for checking or comparing. The scanning of rows is stopped when the first row is found in the subquery of EXISTS function while in IN clause the scanning of rows continues till the end.
One more substantial difference between the usage of both the functions is that the EXISTS function can compare any value with NULL value while in the case of IN clause the NULL values are not compared with anything. In order to understand this, let us consider some examples.
We have one table with the name employee_details whose details are retrieved by using the following query statement –

SELECT * FROM [employee_details]

The output of execution of above query statement is as shown below –

PL SQL exists 4

Now, we will firstly make the use of EXISTS statement in the subquery as NULL and try to retrieve the records of the employee_details table. For this, we will be using the following query statement –

SELECT
*
FROM
employee_details
WHERE
EXISTS (
SELECT
NULL
FROM
dual
);

The output of execution of above query statement is as shown below returning all the rows of the employee details. This was because the EXISTS function compared all the rows with the NULL value.

result

Now, if we try to execute a similar kind of query to retrieve the rows of employee details by using the IN clause in the WHERE clause and passing it NULL values as shown in the below query statement, the output will not return any rows because the IN statement cannot compare the rows with NULL.

SELECT
*
FROM
employee_details
WHERE
employee_id IN(NULL);

The output of execution of above query statement is as shown below –

result 1

The execution of the EXISTS operator is fast as compared to IN clause when the result set of the subquery is substantially large. If the result set of the subquery results in a very small number of rows then the IN clause works faster than the EXISTS clause.

Conclusion

The PL/ SQL exists function is used for checking if any of the rows is returned by the subquery passed as a parameter to this function. This function is mostly used in the WHERE clause which acts as a deciding factor for performing the main query which can be select, insert, update or delete. The return value of the EXISTS function is a Boolean value which is true if even a single row is returned by subquery. Along with that the EXISTS function also stops the execution of the inner subquery if it even finds one row returned by it.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL exists” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PL/SQL NVL
  2. PL/SQL Anonymous Block
  3. PL/SQL stored procedure
  4. PL/SQL GROUP BY

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW