1. More joins
Nice job on those exercises!
2. Other join types
In this video, I will introduce two more join types: cross and self join.
3. Cartesian product (AKA cross product)
Before we get into CROSS JOIN, it's important to understand Cartesian products, also known as cross products.
A cartesian product is when you have two sets of elements and you multiply them to generate all the possible pairs between the elements of the two sets.
4. Cross product on tables
What would it mean to take the cross product of two tables?
Consider each row to be an element of the set, where each row of one table is matched to each row of another table.
Let's take a look at a real-life example.
5. Cross product example
There are five different media types that tracks can be saved in. As of now, each track only comes in only one media type. To help sales, the company would like to make tracks available in all media types.
6. Cross product example
To help, we can use a CROSS JOIN to generate a list of all possible track and media type combinations to keep track of what conversions needs to be done.
7. Self join
Self joins allow you to join a table to itself.
When would you want to do this?
Employees have relationships with each other. The most obvious is the manager and subordinate relationship.
Let's take a look at the employee table.
It contains an employeeid column, as well as a ReportsTo column that refers to another employeeid, specifically the id of the manager of said employee.
8. Self join
If we wanted to get a list of all the employees names and their managers names, we can use a self JOIN, like this!
Because the two tables in the FROM clause are the same, we can use the JOIN clause as SQL will automatically recognize that it's self join.
And, note that because we are using the same table, we have to give them two different aliases - E and M.
9. Let's practice!
It's now your turn to try cross and self joins!