Write a query to identify employees who have never taken a single day of leave in the last two years.

Answer

Use NOT EXISTS to find employees with zero matching rows in the leave table over the last two years.

Solution

SELECT e.employee_id, e.name
FROM Employees e
WHERE NOT EXISTS (
  SELECT 1 FROM LeaveRecords l
  WHERE l.employee_id = e.employee_id
    AND l.leave_date >= CURRENT_DATE - INTERVAL '2 years'
);

Step by Step

1. For every employee, the subquery checks whether any leave record exists for them within the last two years.
2. NOT EXISTS keeps only employees where that subquery finds zero matching rows - meaning no leave taken at all in the window.

Alternative Method: LEFT JOIN with IS NULL

SELECT e.employee_id, e.name
FROM Employees e
LEFT JOIN LeaveRecords l
  ON l.employee_id = e.employee_id AND l.leave_date >= CURRENT_DATE - INTERVAL '2 years'
WHERE l.leave_id IS NULL;

Same logic expressed as a join: employees with no matching recent leave record get a NULL on the right side, which the outer filter then keeps.

Key Exam Traps

• New employees hired within the last two years would correctly appear here too, since they genuinely have no leave records in that window - but this may or may not be the intended interpretation of "never taken a day off"; clarify whether the requirement should only apply to employees who've actually been employed the full two years.
• Employees on unpaid/unlogged leave that isn't recorded in LeaveRecords at all would be incorrectly flagged as "never taken leave" - this query is only as accurate as the completeness of the underlying leave-tracking data.
• Date filter placement in the join's ON clause (not a separate WHERE) is what makes the LEFT JOIN alternative work correctly - the same recurring trap covered in earlier join-based questions in this set.