Using 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, Figure 149 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.

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

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, Figure 151 returns all the elements of the collection where Adams is an element of a collection in the direct_reports column.

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

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

As Figure 152 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, Figure 153 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.

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

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 (IDS).