EXISTS Operator in SQL Server
In this article, I am going to discuss the EXISTS Operator in SQL Server with examples. Please read our previous article where we discussed the SQL Server SOME Operator with examples. At the end of this article, you will understand what exactly EXISTS Operator is and when and how to use this operator in SQL Server.
What is EXISTS Operator in SQL Server?
The SQL Server EXISTS operator is used in combination with a subquery and is considered to be met if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
The EXISTS operator is used to checks the existence of a result of a subquery. Generally, we use this EXISTS operator in WHERE clause to check whether subquery is returning results or not. The EXISTS subquery tests whether a subquery fetches at least one-row or not. When no data is returned then this operator returns ‘FALSE’.
EXISTS Operator Syntax in SQL Server:
- column_name: Name of the column of the table.
- expression1: Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
- table_name: Name of the table.
- WHERE [NOT] EXISTS: Tests the subquery for the existence of one or more rows. If a single row satisfies the subquery clause, it returns Boolean TRUE. When the subquery returns no matching rows the optional NOT keyword returns a Boolean TRUE.
- Subquery: The subquery is a SELECT statement. If the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the subquery does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.
Points to Remember:
SQL statements that use the EXISTS condition are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query’s table. There are more efficient ways to write most queries, that do not use the EXISTS condition.
If you observe above SQL EXISTS operator syntax we will get values where the subquery returns any data or rows otherwise it will not return any data.
Understanding the EXISTS Operator with Examples:
Let us understand EXISTS Operator with some examples. We are going to use the following “EmployeeDetails” and “EmployeeContactDetails” tables to understand EXISTS Operator.
Please use the below SQL Script to create and populate the “EmployeeDetails” and “EmployeeContactDetails” tables with the required test data.
CREATE TABLE EmployeeDetails ( ID INT, Name VARCHAR(50), Gender VARCHAR(10), Department VARCHAR(50), Salary INT, Location VARCHAR(50) ) GO INSERT INTO EmployeeDetails VALUES(1, 'Pranaya', 'Male','IT', 25000, 'Mumbai') INSERT INTO EmployeeDetails VALUES(2, 'Anurag', 'Male','HR', 15000, 'Mumbai') INSERT INTO EmployeeDetails VALUES(3, 'Priyanka', 'Female','IT', 50000, 'Hyderabad') INSERT INTO EmployeeDetails VALUES(4, 'Preety', 'Female','IT', 75000, 'Delhi') INSERT INTO EmployeeDetails VALUES(5, 'Subrat', 'Male','HR', 65000, 'Delhi') INSERT INTO EmployeeDetails VALUES(6, 'Hina', 'Female','Sales', 55000, 'Hyderabad') INSERT INTO EmployeeDetails VALUES(7, 'Sushanta', 'Male','Sales', 20000, 'Delhi') GO
EmployeeContactDetails table with data:
CREATE TABLE EmployeeContactDetails ( ID INT, EmployeeID VARCHAR(50), Email VARCHAR(50), Mobile VARCHAR(10), PinCode VARCHAR(20) ) GO INSERT INTO EmployeeContactDetails VALUES(1,1, 'Pranaya@g.com', '1111111111', '755019') INSERT INTO EmployeeContactDetails VALUES(2,2, 'Anurag@g.com', '2222222222', '755020') INSERT INTO EmployeeContactDetails VALUES(3,5, 'Priyanka@g.com', '3333333333', '755030') INSERT INTO EmployeeContactDetails VALUES(4,6, 'Preety@g.com', '4444444444', '755040') GO
EXISTS Operator With SELECT Statement
Let us see how to use the Exists Operator with SELECT Statement in SQL Server. In the following example, we use the EXISTS operator with the Select statement. Here, the EXISTS condition will check and return all records from the EmployeeDetails table where there is at least one record in the EmployeeContactDetails table with a matching employee ID.
SELECT * FROM EmployeeDetails WHERE EXISTS (SELECT * FROM EmployeeContactDetails WHERE EmployeeDetails.ID = EmployeeContactDetails.EmployeeID);
When you execute the above SQL Query, you will get the following output.
SELECT Statement using NOT EXISTS
The SQL Server EXISTS Operator can also be used with the NOT operator. This will work as opposite to the EXISTS Operator. See the following example for a better understanding. Here, the EXISTS Operator example will return all records from the EmployeeDetails table where there are no records in the EmployeeContactDetails for the matching employee ID column.
SELECT * FROM EmployeeDetails WHERE NOT EXISTS (SELECT * FROM EmployeeContactDetails WHERE EmployeeDetails.ID = EmployeeContactDetails.EmployeeID);
You will get the following output when you execute the above SQL Query.
In the next article, I am going to discuss UNION and UNION ALL SET Operators in SQL Server with some examples. Here, In this article, I try to explain the EXISTS Operator in SQL Server step by step with some examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.