IN

Remarks

Use the IN operator to test whether the result of the outer query is included in the result of the subquery. The result table for the statement includes only rows the outer query returns that have a related row from the subquery.

Examples

The following example lists the names of all students who have taken Chemistry 408:

SELECT p.First_Name + ' ' + p.Last_Name FROM Person p, Enrolls e WHERE (p.id = e.student_id) AND (e.class_id IN

(SELECT c.ID FROM Class c WHERE c.Name = 'CHE 408'))

PSQL first evaluates the subquery to retrieve the ID for Chemistry 408 from the Class table. It then performs the outer query, restricting the results to only those students who have an entry in the Enrolls table for that course.

Often, you can perform IN queries more efficiently using either the EXISTS keyword or a simple join condition with a restriction clause. Unless the purpose of the query is to determine the existence of a value in a subset of the database, it is more efficient to use the simple join condition because PSQL optimizes joins more efficiently than it does subqueries.