TSQL -> EXISTS operator

EXISTS operator returns TRUE if a subquery contains any rows. In the follwing example we want to retrive list of employees from department number 4, only if people from this department produced sales in 1999 :

SELECT a.EmpNo, RTrim(a.FirstName) + ' ' + RTrim(a.LastName) AS EmpName, SUM(b.DocTotal) AS STotal
FROM Employees AS a LEFT JOIN Invoice AS b
  ON a.EmpNo = b.SlpCode
WHERE a.DeptNo = 4
  AND EXISTS
  (SELECT * FROM Invoice AS t1 INNER JOIN Employees AS t2
  ON t1.SlpCode = t2.EmpNo WHERE t2.DeptNo = 4
  AND YEAR(t1.docdate)=1999)
GROUP BY a.EmpNo, RTrim(a.FirstName) + ' ' + RTrim(a.LastName);


sqlexamples.info