EXISTS

What is EXISTS

• EXISTS tests whether a correlated subquery returns any rows.
• Syntax: WHERE [NOT] EXISTS (correlated-subquery).
• It returns TRUE as soon as one matching row is found; it does not build a list of values.
• NOT EXISTS finds rows with no match, which is called an anti-join.

EXISTS vs IN

• EXISTS stops at the first matching row, so it is often faster than IN on large tables.
• EXISTS handles NULLs safely. NOT IN returns no rows if the subquery list contains a NULL; NOT EXISTS has no such problem.
• Use EXISTS when you only need to know whether a match exists, and IN when you need the actual list of values.
• Example:-
  -- Customers who have NO invoices (anti-join) SELECT FNAME, LNAME FROM MM01.CUSTOMER A WHERE NOT EXISTS (SELECT * FROM MM01.INVOICE WHERE INVCUST = A.CUSTNO);