Counts how many elements an array has.
CARDINALITY(<array>)
Returns the total number of elements in an array, however many dimensions it has. For an empty array — 0. The similar ARRAY_LENGTH asks about the length of a specific dimension and gives NULL for an empty array. Handy for finding out how many tags or roles a row has, or for picking rows with a non-empty list.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| tag_count |
|---|
| 3 |
| name | phone | parts |
|---|---|---|
| Emma | +44 7700 900123 | 3 |
| Liam | NULL | NULL |
| Olivia | +44 7700 900456 | 3 |
| Noah | +44 7700 900789 | 3 |
| Sophia | NULL | NULL |
| all_elements |
|---|
| 4 |
CARDINALITY counts all the elements — 4, while ARRAY_LENGTH(…, 1) would give the number of rows — 2.
To check whether an array is empty, write CARDINALITY(tags) = 0: with ARRAY_LENGTH this check doesn't work, because it gives NULL for an empty array.
NULL instead of an array gives NULL, not 0.
A NULL inside an array is an element too: CARDINALITY(ARRAY['a', NULL]) gives 2.