The IN keyword to search for elements in a collection

You can use the IN keyword in the WHERE clause of an SQL statement to determine whether a collection contains a certain element. For example, the following query shows how to construct a query that returns values for mgr_name and department where Adams is an element of a collection in the direct_reports column.

Figure 1. Query

SELECT mgr_name, department 
   FROM manager
   WHERE 'Adams' IN direct_reports

Figure 2. Query result

mgr_name     Sayles
department   marketing

Although you can use a WHERE clause with the IN keyword to search for a particular element in a simple collection, the query always returns the complete collection. For example, the following query returns all the elements of the collection where Adams is an element of a collection in the direct_reports column.

Figure 3. Query

SELECT mgr_name, direct_reports
   FROM manager
   WHERE 'Adams' IN direct_reports

Figure 4. Query result

mgr_name         Sayles
direct_reports  SET {Smith, Waters, Adams, Davis, Kurasawa}

As the result shows, a query on a collection column returns the entire collection, not a particular element within the collection.

You can use the IN keyword in a WHERE clause to reference a simple collection only. You cannot use the IN keyword to reference a collection that contains fields that are themselves collections. For example, you cannot use the IN keyword to reference the projects column in the manager table because projects is a nested collection.

You can combine the NOT and IN keywords in the WHERE clause of a SELECT statement to search for collections that do not contain a certain element. For example, the following query shows a query that returns values for mgr_name and department where Adams is not an element of a collection in the direct_reports column.

Figure 5. Query

SELECT mgr_name, department 
   FROM manager
   WHERE 'Adams' NOT IN direct_reports

Figure 6. Query result

mgr_name     Williams
department   engineering

mgr_name     Lyman
department   publications

mgr_name     Cole
department   accounting

For information about how to count the elements in a collection column, see Cardinality function.