If you work with databases, eventually, you will come across the need to understand the concept behind Cartesian Products. This is the fundamental mathematical principle that underpins all database Joins, and if you are not using Joins then you are probably not getting the real power of a relational database.
For background, relational databases hold data in tables. Each table is made up of one or more columns into which we hold data. Tables are related to each other through keys ( numerical unique identifiers )
For example, we may have a table which contains the details of a customer, and another table which holds the types of accounts they have
In this example, we can see that we have 3 customers, and they are associated with 1 or more accounts. Each account has the customer id as one of its fields which shows the relationship between accounts and customers.
Now we understand the basics of how relationships work, we can look at why the Cartesian Product is at the heart of how we manipulate data in a relational database.
Put simply a Cartesian Product is the result of combining all combinations from 2 sets to produce a 3rd set. This is best described with a diagram as below. We can see that we have 2 tables [1, 2, 3] and [1,2], the product of combining these two tables is the 3rd table
In our database the Cartesian Product would give a table for all possible combinations of customers and accounts regardless of whether they are associated or not, and would look something like this
While not much use in the real world, as the customer do not and should never be related to all accounts, only the accounts they own, it shows how a database works when we join tables. This option is available in all databases as a CROSS JOIN
1 | SELECT * FROM Customers CROSS JOIN Accounts; |
However what we want to do in the real world is use INNER and OUTER JOINs that allow us to make the joins based on matching keys in both tables.
More on this to follow