Welcome to Part 1 of lesson 1 in Module 12 on outer join operators. Specialized but important operators for advanced query formulation. I'm going to start with an important question about outer join operators. What is the most important and prevalent outer join operator in practice? Module 12 provides advanced content on query formulation for learners who seek expert-level knowledge and skills. Events query formulation can provide an edge in the workplace, helping learners reach experts' status with more value to an organization. How to do an operators extending the join operators, are an important part of advanced query formulation. Part 1 of Lesson 1 provides conceptual details about outer join operators in preparation for writing select statements involving outer joins in Part 2 of Lesson 1. The objectives in this lesson involve a conceptual understanding of outer join operators, especially as the operators relate to the join operator. You should be able to use a Venn diagram, and a small sample tables to explain the outer join operators and trace results of outer join operations. For the one-sided outer join operator, you should explain the lack of commutativity, an important property of operators. A Venn diagram uses shapes, typically circles or ovals, to represent relationships between sets of objects. In the first part of this lesson, a Venn diagram depicts the relationship with the join and full join operators. The Venn diagram contains two tables. Table 1, the left table, and Table 2, the right table. The first component generates the join result of the two tables with a matching rows on the join column. The matching occurs just on the join column, not an all columns as in a traditional set operators, union, intersection, and difference. The second component, the full outer join, involves a non-matching rows to the left table, an arbitrary selection of tables. The second component could involve the non-matching rows to the right table. Non-matching means that the result preserves or contain rows in the left table with a false value for the equality comparison on the join column. The third component of a full outer join involves a non-matching rows of the right table. The complete result in the full outer join contains matching rows, unmatched rows in the left table, and unmatched rows in the right table. Now, let's continue the full outer join demonstration, working with small input tables. Small versions of the offering and Faculty tables support tracing a result of rows, improving understanding of the outer join operators. The operation Faculty full join offering combines the tables on the Fac number of columns, Faculty.FacNo and offering FacNo. Note that offer number 4444 contains a null value for offering.FacNo. Indicating that offer number 4444 does not have an assigned Faculty row. The first part of the full outer join is to join part containing three matching rows. Faculty.FacNo, 111-11-1111 matches with the first and third rows on the offering table, OfferNo 1111 and 3333. Faculty.FacNo 222-22-2222 matches with the second row on the offering table with OfferNo 2222. The second part of the full outer join, is the unmatched offering rows. The fourth offering row, OfferNo 4444 has a null value for Offering.FacNo. It does not match any Faculty rows. The result table receives a row with OfferNo 4444 and null values for FacNo and FacName. The null values indicate no matching faculty row. The third part of the full outer join, it's the unmatched Faculty rows. The third Faculty row, FacNo, 333-33-3333 does not match any offering rows. The third Faculty row represents a Faculty member without any assigned offerings, perhaps a new Faculty or research Faculty. Result table receives the fifth row with a null value for OfferNo in values for FacNo and FacName from the third Faculty row. The null values indicate no matching offering row. Let's trace the results of a one-sided outer join operation with the same small sample tables. In a one-sided outer join, the preserve table must be specified. This one-sided outer join operation, offering left join Faculty preserves the non-matching rows of the offering table. The first part of the left outer join is the join part, contain three matching rows. The same result is shown for the full outer join. Note the operation specification above the result column names. Offering left join Faculty provides positional notation. The operator left join means preserve the table on the left offering. The second and last part, or the left outer join, it's the unmatched offering rows. The fourth offering row offer number 4444 has a null value for Offering.FacNo, so it does not match any Faculty rows. The result table receives a row with offer number 4444 and null values for FacNo and FacName. The null values indicate no matching Faculty row. The left outer join operation terminates with a matched rows and unmatched offering rows. No unmatched rows of the Faculty table are considered because offering is positionally the left table. Let's wrap up Part 1 of Lesson 1 about outer join operators. Part 1 of Lesson 1 demonstrated two outer join operators using a Venn diagram and small sample tables. The full outer join generates the match rows, that's the join result, and nonmatch rows of both tables the one-size outer join generates the match rows, that's the join part, and nonmatch rows of one designated table. In answer to the opening question, the one-side outer join is the most important in practice, is Part 2 of Lesson 1 demonstrates. The one-sided outer join applies to a table with a foreign key allowing null values, such as the offering table. For one-sided outer join is not commutative like the subtraction operator with numbers. Table 1, left join Table 2 does not generate the same result as table two left join table one. The one-sided outer join remains a specialized but important operator despite the lack of commutativity in the specification.
