DbSchema Tutorial | SQL EXISTS OPERATOR

---

SQL Tutorial • 09-Oct-2023

by Malik Umair Sajjad

---

1. Introduction to SQL EXISTS Operator

SQL, Structured Query Language, is a domain-specific language used for managing relational databases. One of the powerful operators in SQL is the EXISTS operator.

The EXISTS operator is used to determine the presence of rows in a subquery. It returns TRUE if the subquery returns one or more rows and FALSE if the subquery returns no rows.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery);

Example:

Let’s consider a sample database table called students:

student_id	student_name	age
1	Alice	21
2	Bob	22
3	Charlie	23

Query to find students whose age is greater than 21:

SELECT student_name 
FROM students 
WHERE EXISTS (SELECT * FROM students WHERE age > 21);

Result:

student_name
Alice
Bob
Charlie

Explanation:

The subquery returns students with age greater than 21. Since the subquery returns rows, the main query returns all student names.

2. Key Differences Between SQL EXISTS and SQL IN

Feature	SQL EXISTS	SQL IN
Purpose	Test for the existence of rows	Test for values in a list
Return Value	TRUE or FALSE	TRUE or FALSE
Performance	Usually faster with subqueries that return large results	Can be slower with large lists
Syntax	WHERE EXISTS (subquery)	WHERE column_name IN (value1, value2, …)

3. When to Use SQL EXISTS and When to Use SQL IN

• Use EXISTS: When you want to check the existence of rows in a subquery, especially when the subquery returns a large number of rows.
• Use IN: When you know the exact values to compare against.

4. Using the SQL Exists Operator with Subqueries

Subqueries are queries nested inside another query. The EXISTS operator often works in conjunction with subqueries.

Example:

To find students with age greater than 21:

SELECT student_name 
FROM students 
WHERE EXISTS (SELECT * FROM students WHERE age > 21);

Result:

student_name
Alice
Bob
Charlie

Explanation:

Since the subquery finds students older than 21, the main query returns all student names.

5. SQL NOT EXISTS

The NOT EXISTS operator is used to select rows that don’t satisfy the condition of the subquery.

Example:

To find students with age less than or equal to 21:

SELECT student_name 
FROM students 
WHERE NOT EXISTS (SELECT * FROM students WHERE age > 21);

Result:

student_name
Alice

Explanation:

Alice is the only student with age less than or equal to 21.

6. EXISTS Operator With SELECT Statement

The EXISTS operator can be used with the SELECT statement to filter out rows based on the result of the subquery.

Example:

To select all students if there’s at least one student older than 21:

SELECT student_name 
FROM students 
WHERE EXISTS (SELECT * FROM students WHERE age > 21);

7. SELECT Statement using NOT EXISTS

You can use NOT EXISTS with the SELECT statement to filter rows that don’t meet the subquery’s condition.

Example:

To select all students if there isn’t any student older than 25:

SELECT student_name 
FROM students 
WHERE NOT EXISTS (SELECT * FROM students WHERE age > 25);

Result:

student_name
Alice
Bob
Charlie

Explanation:

As there is no student older than 25, the main query returns all student names.

8. Using EXISTS condition with DELETE statement

The EXISTS operator can be used with the DELETE statement to remove rows based on a condition.

Example:

To delete all students if there’s at least one student older than 25:

DELETE FROM students 
WHERE EXISTS (SELECT * FROM students WHERE age > 25);

9. Using EXISTS condition with UPDATE statement

You can use the EXISTS operator with the UPDATE statement to modify rows based on a condition.

Example:

To update the name of students to ‘Senior’ if there’s at least one student older than 25:

UPDATE students 
SET student_name = 'Senior' 
WHERE EXISTS (SELECT * FROM students WHERE age > 25);

10. Using SQL Exists with Joins

You can combine the EXISTS operator with JOIN operations to filter rows based on conditions across multiple tables.

Example:

Consider a second table courses:

course_id	student_id	course_name
1	1	Math
2	2	History
3	3	Science

To find students who have taken the “Math” course:

SELECT s.student_name 
FROM students s 
WHERE EXISTS (
    SELECT * 
    FROM courses c 
    WHERE c.student_id = s.student_id AND c.course_name = 'Math'
);

Result:

student_name
Alice

Explanation:

Alice is the only student who has taken the ‘Math’ course.

11. Common Mistakes

• Confusing EXISTS with IN. Remember, EXISTS checks for the presence of rows, while IN checks for specific values.
• Not using subqueries with EXISTS. The EXISTS operator always requires a subquery.

12. Frequently Asked Questions (FAQs)

• Q: Can EXISTS be used without subqueries?

  • A: No, EXISTS always requires a subquery to determine the existence of rows.

• Q: Is EXISTS faster than IN?

  • A: Generally, EXISTS can be faster, especially with subqueries returning large results. However, performance can vary based on the database system and specific use case.

13. Practice Questions

1. Write a query to find students who haven’t taken any courses.
2. Write a query to delete students who are younger than 20 and have taken the “History” course.
3. Update the name of students to ‘Junior’ if they haven’t taken the “Science” course.
4. Find students who have taken both “Math” and “History” courses.

Conclusion

Understanding the EXISTS operator is crucial when working with SQL, as it offers a powerful way to filter rows based on the presence of other rows in a subquery. Make sure to practice with real-world scenarios to solidify your understanding. Happy querying!