Selecting Data from Multiple Tables

Using Cross Joins

Your browser needs to be JavaScript capable to view this video

Try reloading this page, or reviewing your browser settings

This video explains how to use Cross Joins to retrieve columns from multiple tables.

Keywords

  • T-SQL
  • Joins
  • Inner Joins
  • Multiple Tables
  • Cross Joins
  • Cartesian Product

About this video

Author(s)
John Deardurff
First online
05 April 2019
DOI
https://doi.org/10.1007/978-1-4842-4905-5_9
Online ISBN
978-1-4842-4905-5
Publisher
Apress
Copyright information
© John Deardurff 2019

Video Transcript

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.