Selecting Data from Multiple Tables

Using Left Outer 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 Left Outer Joins to retrieve columns from multiple tables.

Keywords

  • T-SQL
  • Joins
  • Inner Joins
  • Multiple Tables
  • SELECT
  • Outer Joins
  • Left Join
  • Parent Table

About this video

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

Video Transcript

Hello. Let’s talk about using left outer joins. Previously, we discussed the three different join types. But for this video, we’re going to discuss the left outer join. In this case, we want to return all the records from the parent table, even if there is a matching record or not. For our example, we want to return all the customers, whether or not they’ve placed an order.

So if we look at the code, you can see I’ve replaced inner join with the words, left outer join. And, in this example, we are now returning the values for Bob Smith, Peyton Wayne, Sandy Summerstone, and Penny Layne. They’re not showing up in our records set because the left outer join is returning all the records from the parent table, as well as any of that match. Let’s go look at our code.

Here you can see previously we’ve already written an inner join. Now we’re going to change that inner join into a left outer join. So left outer join. And I’ll go and execute the code. And now you can see we did return Bob Smith, or the customer ID 267, and he has a null for quantity, which means he does not have a value for the quantity because he does not have a record in the Orders table.

And if I scroll down, you could see that our bottom three people– I believe it was Peyton Wayne, Sandy Summerstone, and Mr. Layne– also do not have a value in the quantity field because they are customers who have not placed an order. Now when I do write my left outer join, the outer statement is actually optional. I could just type in left join, and get the exact same result.

So you can see there’s Bob Smith, 267. If I scroll down, there’s Peyton Wayne, Sandy Summerstone and Mr. Layne. Now what if I just want to see the parent records? Well, we can use and Is Null statement in our Where clause. I can say where quantity– quantity is null. And that’s going to show us all the customers who did not place an order. So maybe I want to send them coupons or a discount to get them into our store.

So in this video segment, we’ve discussed how to use left outer joins.