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.

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

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 –

SELECT * FROM [employee_details];

The output of the execution of the above query is –

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

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 –

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.

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 –

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

This is a guide to PL/SQL exists. Here we discuss Introduction, syntax, along with examples respectively. You may also have a look at the following articles to learn more –

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