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.
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
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.
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.
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.
2. Using EXISTS with UPDATE Statement
Consider the below product table to understand the functioning of EXISTS function with the UPDATE statement.
Here, we will update the category_id of the book with the help of the UPDATE and EXISTS function combination
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.
Here, we will fetch all records of the employee table which does not exist in the department table.
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.
Here, we will delete all records in the product table whose product is Electronics.