Hello. And welcome to working with Left Outer Join. In this video, we will learn about creating a result set by joining rows from two tables. At the end of this lesson, you will be able to explain the syntax of the Left Outer Join Operator and interpret the result set. A join combines the rows from two or more tables based on a relationship between certain columns in these tables. There are two types of table joins, Inner Join and Outer Join. And Outer Join is a specialized form of Join. And there are three types of Outer Join, Left Outer Join, Right Outer Join, and Full Outer Join. This video explains Left Outer Join or simply called Left Join. Looking at the diagram, the terms left and right refer to the table on the left hand side and the right hand side of the diagram. In this diagram table 1 is the left table. A left join matches the results from two tables and displays all the rows from the left table, and combines the information with rows from the right table that match the criteria specified in the query. In this diagram, a Left Join matches all the rows from table 1 and combines the information with rows from table 2 that match the criteria specified in the query. Based on our simplified library database model, if we want to check the status of all people to see what books they have borrowed, this information is split between two tables: the Borrower table and the Loan table. So we need to identify the relationship between the two tables. In this example, we do this by matching the Borrower ID. Notice that the column Borrower ID exists in the borrower table as the primary key, and the loan table as the foreign key. A primary key uniquely identifies each row in a table. A foreign key is a set of columns referring to a primary key of another table. In an Outer Join, the first table specified in the FROM clause with the sequel statement is referred to as the left table, and the remaining table is referred to as the right table. This is the syntax of the select statement for a Left Join. In this example, the borrower table is the first table specified in the FROM clause of the select statement. So the borrower table is the left table, and the loan table is the right table. Notice that in the FROM clause, we identify the borrower table as B, and the loan table as L. This use of the letter B and the letter L is referred to as an alias. In this Join, each column name is prefixed with either the letter B or L. This helps indicate which table each column is associated with. Using an alias is much easier than prefixing each column name with the whole table name. In the FROM clause, borrower is listed on the left hand side of the Join operator. Therefore, we will select all rows from the borrower table, and combine this with the contents of the loan table based on the criteria specified in the query. In this example, the criteria is the Borrower ID column. For a Left Join, we will select the following columns from the borrower table: Borrower ID, last name, and country. And we will also select the following columns from the loan table: Borrower ID and loan date. The Left Join selects each borrower ID in the borrower table and displays the loan date from the loan table. And here is the result set. The result set shows each Borrower ID from the borrower table, and the loan date for that borrower. Notice there is a loan date for the first five rows: D1-D5. However, for the last three rows, the borrow ID and loan date show null values. Let's take a closer look at this. A null value indicates an unknown value. When using a Left Join, if the right table does not have a corresponding value, a null value is returned. In this example, borrowers Peters, Li, and Wong, whose borrower IDs are D8, D6, and D7 have never taken a book out on loan. Therefore, there is no corresponding Borrower ID value in the loan table. In this example, the Left Join displayed all rows from the left table, the borrower table, and combine the information with rows from the right table, the loan table. The borrower table contained rows for borrower ID D8, D6, and D7. However, the loan table did not contain rows for these Borrower IDs. Therefore, the result set displayed null values for these Borrower IDs. Now you can explain the syntax of the Left Outer Join Operator, and interpret the result set including a null value. Thanks for watching this video.
