UNION and UNION All operators
Try reloading this page, or reviewing your browser settings
This video explains how to use Cross Joins to retrieve columns from multiple tables.
- Inner Joins
- Multiple Tables
- Cross Joins
- Cartesian Product
About this video
- John Deardurff
- First online
- 05 April 2019
- Online ISBN
- Copyright information
- © John Deardurff 2019
In this video segment, we’re going to discuss using cross joins. Now, cross joins are sometimes known as Cartesian products. This is where I want to combine all the records from one table with all the records in the other table. For example, let’s say I have a list of employees, and I want to see if they’ve taken Transact SQL courses. So I have a table with eight employees, and then I have a products table of four products or four SQL courses. And I want to retrieve a list that matches each employee with each course.
To do this, I’m going to create a cross join. Here, I have written a cross join where I’m selecting the first name and last name from the employee’s table and the product ID and product name from the products table. With our join statement, I’ve typed in the word “cross join.” Now, you may notice I do not have an on statement because I’m not retrieving records based on a value from one table equaling a value from the other table. I want to match every record from the employees to every record in the products table.
So let’s see this in our code. Here I’ve opened up Management Studio. And I’ve already selected first name and last name from the employees table. And if I execute that code, you could see that we have eight records, eight employees. If I look at my products table, you can see that I have four courses.
So I want to match each employee with each course. So there’s eight employees and four courses. If I multiply those eight times four, I should end up with 32 records when I do a cross join. So I’m going to go ahead and delete the products table and then just add it into my select statement up here, product ID, product name. And then I’m going to alias my employees table as E cross join, production.products as p.
Now, to make it easier to see our results, I am going to order by employee ID so we can see each employee map to each course. So I execute. And you see that Jackson Johnson has indeed been mapped to all four courses. If I scroll down, we see Amari Underwood, Becky Lightyear, Susan Spencer, Bucky Denton. They’ve all been mapped to the four courses. And again, I did have 32 rows because there was eight employees and four courses, and I mapped each employee with each course. So in this video segment, we’ve discussed how to use cross joins.