The LEFT JOIN - SQL Tutorial

- [Instructor] A left outer join combines all the records from the left table with any matching records from the right table. As shown in our Venn diagram, the concept of left table and right table depends entirely on the order these tables are listed in the join statement. So for example, our SQL statement here has listed invoices first. Then after the left outer join, it lists the customer table. And in this instance, our invoice table is the left table and our customer table is the right table. With this type of join, everything in our invoice table will be displayed. Since customer one did not order any songs, that particular record is omitted. However, as we can see in our simplified invoice and customer table, we are combining all five records from the invoice table with only three records from the customer table. Remember, there is no record for customers number one or number five in the invoice table, and customer number two has produced or purchased two songs and created two invoice records. Unlike the inner join, which matched an equal number of records between each table, let's now take a look at the output of this query to understand how the SQL browser handles it. Now, this SQL query or syntax for the left outer join is very similar to what we've used for the inner join. We're simply replacing inner join with left outer join instead. Now, when we take a look at the output of the left outer join, we see that the SQL browser has added nulls or null data to our results set. Remember that we have no information in the customer table about customer six. Adding null data is how the SQL browser handles the fact that we are trying to match five records from the invoice table to only four records from the customer table. Left joins are useful because they allow us to see discrepancies in our data. We can produce lists of customers that have not generated invoices or search for data that has been removed in the right table but still exist in the left. Now let's move on to take a look at right outer joins.