SQL Exists: Usage, Syntax, and Examples

To check if a correlated subquery yields any results, use the SQL EXISTS condition. The EXISTS condition evaluates to TRUE if the subquery returns at least one row, and to FALSE otherwise. To filter data according to whether specific conditions are met, the EXISTS operator can be used in a variety of SQL statements, including SELECT, UPDATE, INSERT, and DELETE.

SQL EXISTS What is it?

The main purpose of the EXISTS condition is to verify that the rows that a subquery returns actually exist. It's frequently employed in situations where you want to take action based on the outcome of determining whether a record exists in a related table.

Syntax

SELECT column_name(s)
FROM table_name
WHERE EXISTS (
    SELECT column_name(s)
    FROM subquery_table
    WHERE condition
);

1. EXISTS: The boolean operator that determines whether rows are returned by a subquery.
2. Subquery: A subquery is a nested SELECT query that yields information for analysis.
3. Condition: The subquery was subject to the condition.

SQL EXISTS Examples

Examine the following two relationships: "Addresses" and "Employees."

Employees

Addresses

As an illustration

Using EXISTS with SELECT

Fetch the employees who have associated the address.

Query

SELECT E.[Name], E.[Mno]
FROM Employees AS E
WHERE EXISTS 
(
	SELECT * 
	FROM Addresses AS A
    WHERE A.EmpId = E.Id
);

Output

Using NOT with EXISTS

Fetch the employees who have not associated the address.

Query

SELECT E.[Name], E.[Mno]
FROM Employees AS E
WHERE NOT EXISTS 
(
	SELECT * 
	FROM Addresses AS A
    WHERE A.EmpId = E.Id
);

Output

Using EXISTS condition with DELETE statement

Delete the address from the Addresses Table whose Name is “Jacky Patel”.

Query

DELETE A
FROM Addresses AS A
WHERE EXISTS
(
	SELECT *
	FROM Employees AS E
	WHERE E.Id = A.EmpId 
		AND E.Name = 'Jacky Patel'
);

SELECT * FROM Addresses;

Output

When SQL EXISTS Should Be Used?

The following situations make the EXISTS condition especially helpful.

• Verifying Data Existence: Before carrying out an action (such as choosing, updating, or deleting data), you can use EXISTS to see if related data is present in another table.
• Performance: Because EXISTS stops searching as soon as a match is found, it is frequently more effective than IN when working with large datasets.
• Correlated Subqueries: When a subquery refers to the values of the outer query, EXISTS is the best option.

The Distinctions Between IN and EXISTS.

• Whereas IN determines whether a value matches any value from a list or subquery result, EXISTS determines whether rows exist.
• When the subquery yields a lot of rows, EXISTS is more effective.
• Small datasets or static lists of values are good candidates for IN.

Conclusion

For database professionals, the SQL EXISTS condition is a vital tool for verifying whether records in a subquery exist. Depending on whether related data is present, you can use EXISTS to carry out a variety of operations, including SELECT, UPDATE, INSERT, and DELETE. EXISTS offers a strong, effective method of filtering and manipulating data, whether you're doing data integrity checks or determining whether customers have placed orders.

We learned the new technique and evolved together.

Happy coding!