Oracle CARDINALITY Function

Nested tables are Oracle's collection type for SQL: a column or expression can hold a whole list of values, such as the destinations of an airport. CARDINALITY returns how many elements such a collection has. It is the collection counterpart of COUNT, and the usual way to test whether a collection is empty.

Code for This Guide

The main example is in the examples/misc-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

cardinality(nested_table)

The argument must be a nested table, a collection type created with CREATE TYPE ... AS TABLE OF. The examples use this type:

Setup:

create or replace type t_codes as table of varchar2(10);

Count Elements

The example counts the elements of a collection with a duplicate, and of the same collection after SET has removed the duplicate.

Example:

select cardinality(t_codes('DXB', 'LHR', 'DXB', 'SIN'))      as items,
       cardinality(set(t_codes('DXB', 'LHR', 'DXB', 'SIN'))) as distinct_items,
       set(t_codes('DXB', 'LHR', 'DXB', 'SIN'))              as set_result
from   dual;

Output:

   ITEMS    DISTINCT_ITEMS SET_RESULT
________ _________________ __________________
       4                 3 [DXB, LHR, SIN]

t_codes('DXB', 'LHR', 'DXB', 'SIN') is a type constructor: it builds a collection from its arguments. CARDINALITY counts all four elements, duplicates included.

Empty, NULL, and NULL Elements

An empty collection and a NULL collection are different things, and CARDINALITY tells them apart.

Example:

select cardinality(t_codes())                    as empty_collection,
       cardinality(cast(null as t_codes))         as null_collection,
       cardinality(t_codes('DXB', null, 'LHR'))   as with_null_element
from   dual;

Output:

   EMPTY_COLLECTION    NULL_COLLECTION    WITH_NULL_ELEMENT
___________________ __________________ ____________________
                  0                                       3

An empty collection has 0 elements, while a NULL collection has no cardinality at all and returns NULL. A NULL element still counts as an element. To test for "no elements" safely, use cardinality(x) = 0, or the condition x IS EMPTY, which is not true for a NULL collection.

Things to Know

• CARDINALITY works on nested tables. For a VARRAY, use the COUNT method in PL/SQL or count the rows of TABLE(varray) in SQL.
• In PL/SQL, the COUNT method gives the same number for a nested table variable.
• Combine it with COLLECT to count the values gathered per group.

Related Guides

• How NULL Works in Oracle Conditions

Conclusion

CARDINALITY returns the number of elements in a nested table, counting duplicates and NULL elements, returning 0 for an empty collection and NULL for a NULL one.