What is cardinality in SQL?

In SQL, cardinality refers to the unique values in a column or set of columns. It is an important concept in database design, as it determines the maximum number of occurrences for each value in a column.

Three types of cardinality

There are three types of cardinality:

High cardinality: This type of cardinality is characterized by a large number of unique values in a column or set of columns. For example, a column that contains customer IDs would have high cardinality because each customer has a unique ID.
Low cardinality: This type of cardinality is characterized by a small number of unique values in a column or set of columns. For example, a column that contains gender would have low cardinality because there are only two possible values (male and female).
No cardinality: This type of cardinality is characterized by a lack of unique values in a column or set of columns. For example, a column that contains the value "NULL" for every row would have no cardinality because there are no unique values in the column.

Performance of queries

Cardinality is an important concept in database design because it can affect the performance of queries.

For instance, a query that filters on a high-cardinality column is likely to be faster than a query that filters on a low-cardinality column because there are fewer values to compare.

Similarly, a query that filters on a column with no cardinality will be slower than a query that filters on a column with high or low cardinality because there are no unique values to compare.

How to find cardinality in SQL

To find the cardinality of a column or set of columns in SQL, you can use the COUNT and DISTINCT functions in combination with the GROUP BY clause.

The COUNT function is used to count the number of rows in a table, and the DISTINCT keyword is used to count only the unique values in a column. The GROUP BY clause is used to group the rows in a table by the values in a specific column.

Here is an example of how to find the cardinality of a column in SQL:

SELECT COUNT(DISTINCT ColumnName)
FROM TableName

This query will return the number of unique values in the ColumnName column of the TableName table.

Here is another example that uses the GROUP BY clause to find the cardinality of multiple columns in SQL:

SELECT COUNT(DISTINCT Column1), COUNT(DISTINCT Column2)
FROM TableName
GROUP BY Column1, Column2

This query will return the number of unique values in the Column1 and Column2 columns of the TableName table, grouped by the values in the Column1 and Column2 columns.

As you can see, the COUNT and DISTINCT functions are used to find the cardinality of a column or set of columns in SQL. The GROUP BY clause can be used to group the results by the values in a specific column.

What is cardinality in SQL?