SELECT from Multiple Tables

Welcome back. In this video segment, we’re going to jump right into a demonstration on how to select data from multiple tables. So as you can see here, I’ve already written some code to use the bookstore database. And I’m selecting all the columns from the sales.customers table and all the columns from the sales.orders table.

If you do not have a copy of the bookstore database, please watch the introduction video for these videos. Also it is best practice in a production environment not to select all the columns from a table. I’m selecting all the columns here so that we can see what data is actually in these tables.

So to start off, I’m going to select all the columns from the sales.customers, so that we can identify our data. You may notice that we have 10 rows for our 10 customers. We’re going to try to keep it simple with our data. Next, if we look at the Orders table, you can see here are the orders that my customers made. Also I have employee ID for the employees that sold these items and I have a product ID that would tie to the Products table to tell me the information about the products, such as product name and manufacturer.

But what if I wanted to see information from both tables, I wanted to get columns from both tables? I could highlight both select statements and execute. And you would see I would have– if I scroll down, I would have two separate results sets, one result set for customers and one result set for orders.

But what if I wanted to get just a single result set with columns from the two tables? This is where I need to join my tables. Now, there are several different types of joins– inner joins, left outer joins, right outer joins. We’ll discuss those coming up in additional videos. But for now, we’re just going to learn the syntax of how to join these tables together.

So I’m going to hide my results pane. I normally just use Control+R to hide my results pane. But I’ll hide my results pane so we have some room to work. And I’ll go ahead and put the from statement on the next line. And I want to join the sales.order table. So I’ll go ahead and delete the select asterisks from and join the sales.orders table.

Now, when I am connecting these two tables together, I do need to connect them on a specific field or a field that’s going to appear in both tables. In our case here, if we were being observant, we may have noticed that there was a Customer ID field in both tables. So I’m going to type on, to specify in what field I want to connect these tables on, sales.customers– I have [? intellisense. ?] I’ll click my Tab to fill that in– .customerID. And I want that value to equal the sales.orders.custID.

Now, the field I’m connecting on does not have to have the same name. But they do have to have the same type of data or data type. Now I can go ahead and execute this query. And as you can see, I have a single result set with all my customers. If I scroll a little bit to the right, you can see there are the orders that each customer made. So now, I’m getting all the columns for both the Customers table and the Orders table.

But what if I just wanted specific columns? So let’s say I want to get the customer ID from the Customers table– customer ID– the last name from the Customers table, and we’ll get the quantity. We’ll just work on the quantity from the Orders table. Now, when I select, Execute, now I just have those three values, those three columns. But it’s data that’s coming from both tables.

Now, one way I could simplify my code is to use table aliases. Instead of having to type out sales.customers and sales.orders, I can alias my tables. So I can alias as C and alias as O. And that way, it might make it easier to type in my table names. This really is helpful when I start job working with even more tables, more than just two tables, if I want to work with three or five or 50 tables and I want to combine them together. Let’s get that correct there. And you can see now when I execute, I’m still getting the same information. I just use those table aliases to simplify joining those tables.

So in this video segment, we have discussed how to join tables together.