A query can use JOINs to retrieve descriptive data while EXISTS verifies that another required relationship is present.
Complex business rules may require both descriptive information and existence-based filtering.
Show departments that have employees who have placed orders.
SELECT DISTINCT
d.DepartmentID,
d.DepartmentName
FROM Departments d
INNER JOIN Employees e
ON d.DepartmentID = e.DepartmentID
WHERE EXISTS (
SELECT 1
FROM Orders o
WHERE o.EmployeeID = e.EmployeeID
);Used in business reporting, eligibility analysis, operational dashboards, and validation queries.
- JOIN retrieves related information.
- EXISTS checks another relationship.
- The two techniques can work together.
- DISTINCT may be needed depending on the required grain.
- Always define the intended result grain.
Can JOIN and EXISTS be used together?
Yes. JOIN can retrieve related information while EXISTS applies an additional existence rule.
Why might DISTINCT be required?
Multiple employees or related records may otherwise produce repeated department rows.