SQL EXISTS

This article will help you in providing a detailed explanation of the working of exists function in SQL with numerous examples. First, let us understand what is Exists function and what is its syntax.

EXISTS() function is used to check whether there is any record present in a subquery. If a subquery contains at least one record, then it returns true. Below is a list of the syntax for the SQL EXISTS() function. It is used combinedly with SELECT, UPDATE, DELETE statement and also used with WHERE CLAUSE.

Syntax:

The EXISTS keyword comes before the subquery in this syntax, which is present in brackets. After that, it has the subquery which returns rows by evaluating it. The EXIST function either returns true or false based on the subquery, if subquery generates at least one record then the EXISTS function returns true, otherwise it returns false.

The EXISTS function can be used to filter results, but it can also be used with other logical operators like NOT, AND, and OR to build more filtering criteria. For instance, you could update all records in a database based on the existence of related data in another table, or you could use EXISTS to locate all customers who have placed purchases in the past month.

Usage of EXISTS function

• searching for records in a many-to-many relationship: EXIST function can be used in identifying a record present in a join table for a many-to-many relationship. For example, fetching out all the students who are studying in a specific department.
• Fetching records based on the presence of related records: This SQL function can be used to fetch records based on the presence of related records in another table. For example, finding all orders that have associated order details.
• Enhancing queries: The processing of the query is enhanced by EXISTS function because it shows only the required data. Suppose, we want to find out the first working day of each employee without using a self-join.

Consider the below examples to understand the functioning of SQL EXISTS() Function.

1. Using EXISTS with SELECT Statement

Consider below Student and Student_joining tables for understanding the functioning of EXISTS() function.

Table: Student

Table: Student_joining

Now let us extract all the records from the student table which are having at least one record in the student_joining table with the same Roll_no.

Output of above query:

This is also an other example which also uses the EXISTS function with select statement. Let's take the following employee and department tables.

Table: employee

Table: department

Now, we are going to use the following query to fetch all the records from the employee table based on emp_id present in both the columns.

Output:

2. Using EXISTS with UPDATE Statement

Consider the below product table to understand the functioning of EXISTS function with the UPDATE statement.

Table: product

Here, we will update the category_id of the book with the help of the UPDATE and EXISTS function combination

Output:

3. Using NOT with EXISTS

Consider the below 2 tables(employee, department) to understand the Functioning of EXISTS function when combinedly used with the NOT function.

Table: employee

Table: department

Here, we will fetch all records of the employee table which does not exist in the department table.

Output:

4. Using EXISTS with DELETE statement

Consider 2 tables product and product_category. These 2 tables are used to show how EXISTS function works in combination with the DELETE statement.

Table: product

Table: product_category

Here, we will delete all records in the product table whose product is Electronics.

Output: