Updated on December 4, 2024

EXISTS and NOT EXISTS

Estimated reading: 2 minutes 16 views

1. What is the difference between the EXISTS and NOT EXISTS keywords?

EXISTS: The EXISTS keyword is used to check if a subquery returns any records. It evaluates to true if the subquery returns one or more records, otherwise, it returns false.
NOT EXISTS: The NOT EXISTS keyword is used to check if a subquery does not return any records. It returns true if the subquery does not return any rows.

2. Write a query to find rows in one table that have no matching rows in another.

To find rows in one table that do not have matching rows in another table, you can use NOT EXISTS with a subquery.

Example (with NOT EXISTS):
Find employees who do not have a department assigned to them:

				
					SELECT e.name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.id = e.department_id
);

This query checks for employees who do not have a corresponding department (i.e., no matching record in the departments table for the employee’s department_id).
The NOT EXISTS ensures that only employees without a matching department are selected.

3. Write a query to find rows in one table that have matching rows in another (using EXISTS).

To find rows in one table that have matching rows in another table, you can use EXISTS with a subquery.

Example (with EXISTS):
Find employees who are assigned to a department:

				
					SELECT e.name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.id = e.department_id
);

This query checks for employees who have a corresponding department in the departments table (i.e., the department_id in the employees table matches an id in the departments table).
The EXISTS ensures that only employees with a matching department are selected.

Leave a Comment Cancel reply

CONTENTS