What is cardinality in SQL?
The term cardinality actually has two different meanings depending on the context of it’s usage – one meaning is in the context of data modeling and the other meaning is in the context of SQL statements. Let’s go through the first meaning – when the word cardinality is used in the context of data modeling, it simply refers to the relationship that one table can have with another table. These relationships include: many-to-many, many-to-one/one-to-many, or one-to-one – whichever one of these characteristics a table has in relationship with another table is said to be the cardinality of that table. An example will help clarify further.
An example of cardinality in data modeling
Suppose we have three tables that are used by a company to store employee information: an Employee table, an Employee_Salary table, and a Department table. The Department table will have a one to many relationship with the Employee table, because every employee can belong to only one department, but a department can consist of many employees. In other words, the cardinality of the Department table in relationship to the employee table is one to many. The cardinality of the Employee table in relationship to the Employee_Salary table will be one to one, since an employee can only have one salary, and vice versa (yes, two employees can have the same salary, but there will still be exactly one salary entry for each employee regardless of whether or not someone else has the same salary).
Example of Cardinality in SQL
The other definition of cardinality is probably the more commonly used version of the term.
In SQL, the cardinality of a column in a given table refers to the number of unique values that appear in the table for that column. So, remember that the cardinality is a number. For example, let’s say we have a table with a “Sex” column which has only two possible values of “Male” and “Female”. Then, that “Sex” column would have a cardinality of 2, because there are only two unique values that could possibly appear in that column – Male and Female.
Cardinality of a primary key
Or, as another example, let’s say that we have a primary key column on a table with 10,000 rows. What do you think the cardinality of that column would be? Well, it is 10,000. Because it is a primary key column, we know that all of the values in the column must be unique. And since there are 10,000 rows, we know that there are 10,000 entries in the column, which translates to a cardinality of 10,000 for that column. So, we can come up with the rule that the cardinality of a primary key column will always be equal to the number of records in the same table.
What does a cardinality of zero mean?
Well, if a column has a cardinality of zero, it means that the column has no unique values. This could potentially happen if the column has all NULL’s – which means that the column was never really used anyways.