---
title: "Cardinality"
description: "Cardinality refers to the number of distinct values in a column or dataset, or, in data modeling, the nature of a relationship between two tables (such as one-to-many)."
canonical: "https://motherduck.com/glossary/cardinality/"
related:
  - title: "Database Concepts | MotherDuck Docs"
    url: "https://motherduck.com/docs/concepts/database-concepts/"
  - title: "Text-to-SQL, Data Modeling for AI, and MotherDuck MCP | MotherDuck"
    url: "https://motherduck.com/videos/text-to-sql-data-modeling-llms-mcp-dives/"
  - title: "From Data Lake to Lakehouse: Can DuckDB be the best portable catalog?"
    url: "https://motherduck.com/blog/from-data-lake-to-lakehouse-duckdb-portable-catalog/"
gated_asset:
  title: "DuckLake on MotherDuck"
  url: "https://motherduck.com/product/ducklake/"
---

# Cardinality

> Cardinality refers to the number of distinct values in a column or dataset, or, in data modeling, the nature of a relationship between two tables (such as one-to-many).

## Overview

"Cardinality" shows up in two related but distinct contexts in databases: as a property of a column's data (how many distinct values it has), and as a property of a relationship between tables in a data model (how many rows on one side can relate to rows on the other).

## Column Cardinality

A column's cardinality is its number of distinct values relative to its total row count. A boolean or status column with only a handful of possible values has **low cardinality**; a primary key or UUID column, where every value is unique, has **high cardinality**. Cardinality matters a great deal for both compression (low-cardinality columns compress extremely well with dictionary encoding) and query optimization (a filter on a high-cardinality column is usually far more selective, and therefore more useful to index or prune on, than a filter on a low-cardinality one).

## Cardinality in Data Modeling

In schema design, cardinality describes the shape of a relationship: one-to-one, one-to-many, or many-to-many. A `customers`-to-`orders` relationship is typically one-to-many — one customer can have many orders, but each order belongs to exactly one customer — which determines how foreign keys and join cardinality behave in queries against that schema.

## Why Cardinality Matters for Query Optimization

A cost-based optimizer relies heavily on cardinality estimates — how many rows will come out of a scan, filter, or join — to choose things like join order and join algorithm. Getting these estimates right is one of the hardest and most consequential parts of query optimization; a badly wrong cardinality estimate is one of the most common causes of a slow query plan.

## DuckDB and Cardinality

DuckDB's optimizer maintains per-column statistics, including distinct-value counts, to estimate cardinality for join ordering and other decisions. For getting an exact or approximate distinct count directly, DuckDB provides `count(DISTINCT col)` for an exact answer and `approx_count_distinct(col)`, based on the HyperLogLog algorithm, for a much faster approximate answer on large columns:

```sql
SELECT approx_count_distinct(customer_id) AS approx_unique_customers
FROM orders;
```

`EXPLAIN ANALYZE` also reports each plan operator's estimated cardinality (EC) next to its actual row count, which is a useful way to spot where the optimizer's estimates went wrong.