What is cardinality in SQL?

Cardinality in SQL refers to the number of unique values in a specific column or set of columns within a table. It's a crucial concept for understanding data distribution, database design, and optimizing query performance.

Understanding Cardinality

At its core, cardinality quantifies the uniqueness of data. A column with a high number of distinct values is said to have high cardinality, while a column with many duplicate values has low cardinality. For instance, a column storing unique user IDs would typically have high cardinality, whereas a 'gender' column would have low cardinality.

Database systems, particularly query optimizers, heavily rely on cardinality statistics to make informed decisions about how to execute queries efficiently. It influences the choice of indexes, join algorithms, and overall query plans.

Types of Cardinality

Cardinality can be broadly categorized into high, low, and medium, each with different implications for database performance and indexing strategies.

• High Cardinality: Columns with a large number of unique values, often approaching the total number of rows. Examples: PRIMARY KEY columns (like user_id, order_id), email_address, SSN.
• Low Cardinality: Columns with a small, limited number of unique values. Examples: gender ('Male', 'Female', 'Other'), status ('Active', 'Inactive', 'Pending'), boolean flags ('True', 'False').
• Medium Cardinality: Columns with a moderate number of unique values, somewhere between high and low. Examples: country, zip_code (in a regional dataset), department_name.

Cardinality and Table Relationships

Cardinality is also fundamental when defining relationships between tables in a relational database. It describes how many instances of an entity are related to how many instances of another entity.

• One-to-One (1:1): Each record in Table A relates to exactly one record in Table B, and vice-versa. (e.g., user and user_profile where a profile is optional for each user).
• One-to-Many (1:M): Each record in Table A can relate to one or more records in Table B, but each record in Table B relates to only one record in Table A. (e.g., customer to orders).
• Many-to-Many (M:N): Each record in Table A can relate to one or more records in Table B, and each record in Table B can relate to one or more records in Table A. This often requires an intermediary 'junction' table. (e.g., students to courses).

Why is Cardinality Important?

• Indexing: Columns with high cardinality are generally good candidates for indexing, as an index can quickly narrow down the search space. Columns with very low cardinality are often poor candidates because scanning the entire table might be faster than using an index.
• Query Optimization: The database's query optimizer uses cardinality statistics to estimate the cost of different execution plans. Accurate cardinality estimates lead to more efficient query plans.
• Join Performance: Understanding the cardinality of join keys helps predict the performance of join operations. Joining on high-cardinality columns is often more efficient than on low-cardinality columns.
• Data Storage: While not directly about storage size, extremely low cardinality on indexed columns can lead to index bloat without significant performance benefits.
• Data Integrity and Design: It helps in choosing appropriate data types and enforcing constraints, ensuring data quality and efficient data modeling.

Checking Cardinality in SQL

You can determine the cardinality of a column by counting the number of distinct values it contains. This is often done using the COUNT(DISTINCT column_name) aggregate function.

SELECT COUNT(DISTINCT column_name) AS cardinality
FROM your_table_name;