Left Join allows us to pull data from more than one table when there is a match in at least the left table.
When to use
Use it when you know the data exists in at least the left table.
SELECT [Last Name], [Blue Widget]
LEFT JOIN [dbo].[Orders] ON [Current_Customers].[Customer ID] = [Orders].[Customer ID]
This will display the last name and number of blue widgets purchased by each customer ID.
Similarly to OUTER JOIN, with LEFT JOIN only one table needs to have the thing that you designate (e.g. Customer ID) in order to work. In this case, it is the table on the left that needs to have that thing in common. The table on the left is the first table that you list, which in this example is the Current Customers table.
This means if you have a customer who has a customer ID in the Current Customers table, but not the Orders table, they will still show up in your results. They will just have NULL values in the Blue Widgets column, as they have not made any orders.
Hints & tips
- Left Join pulls data when there is a match in at least the left table.
Lesson notes are only available for subscribers.