Database Reference
In-Depth Information
IN set membership evaluates an expression as being within a set of
elements. Because IN implies set membership, IN allows multiple-
row returning subqueries.
expression [ NOT ] IN (expression)
IN is often used to check membership of one element in a list of
elements. This example checks against a list of literal items:
SELECT NAME FROM ARTIST
WHERE COUNTRY IN ('USA', 'Canada');
This example uses a subquery to create the list for IN membership
to check against:
SELECT NAME FROM ARTIST WHERE ARTIST_ID IN
(SELECT GUESTARTIST_ID FROM GUESTAPPEARANCE);
EXISTS checks for membership as IN does with a few differences: (1)
EXISTS only allows an expression on the left; (2) EXISTS is some-
times faster; and (3) probably most significantly, EXISTS allows a
correlation and index matching between a calling query and a sub-
query. Like the IN condition, EXISTS implies set membership and
allows multiple-row returning subqueries.
[ NOT ] EXISTS (expression)
Note:
IN does allow passing of correlated values into subqueries but
1
EXISTS is the more efficient option.
In this example, the artist's name is retrieved whenever the artist is
found in the GUESTAPPEARANCE table:
SELECT NAME FROM ARTIST WHERE EXISTS
(SELECT GUESTARTIST_ID FROM GUESTAPPEARANCE);
