EXISTS Special Operator

To test whether or not a subselect retrieves any records, the EXISTS or NOT EXISTS special operators are used. The following query retrieves the names of all suppliers only if there is at least one part being supplied in quantities greater than zero:

SELECT sname
FROM supplier
WHERE EXISTS
  (SELECT sno
  FROM partsupp
  WHERE qty > 0)