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.