The CARDINALITY function counts the number
of elements that a collection contains. You can use the CARDINALITY function
with simple or nested collections. Any duplicates in a collection
are counted as individual elements. The following query shows a query
that returns, for every row in the manager table, department values and the number of elements in
each direct_reports collection.
Figure 1: Query
SELECT department, CARDINALITY(direct_reports) FROM manager;
Figure 2: Query result
department marketing 5
department engineering 7
department publications 4
department accounting 3
You can also evaluate the number of elements in a collection from
within a predicate expression, as the following query shows.
Figure 3: Query
SELECT department, CARDINALITY(direct_reports) FROM manager
WHERE CARDINALITY(direct_reports) < 6
GROUP BY department;
Figure 4: Query result
department accounting 3
department marketing 5
department publications 4