 Hello everyone, I am Mrs. Sunita Door. The topic covered is joint relation left outer joint. In previous video, we considered the types of joint and different joint condition. In this video, we are going to cover the topic left outer joint, a type of outer joint. The outer joint operation compute the joint and then add the tuples from one relation that does not match tuples in the another relation to the result of the joint. This outer joint operation is used to avoid the loss of the information. Learning outcome. At the end of the session, students will be able to write the queries using left outer joint operation for given relations. As we know that there are following joint types and the joint conditions as shown in the table. These three joint conditions natural on and using are used with all these joint types that is inner joint, left outer joint, right outer joint and the full outer joint. Consider outer joint. The outer joint operation is an extension of the joint operation to deal with the missing of information. It computes the joint and then adds the tuples from one relation that does not match the tuple in the other relation to the result of the joint. The outer joint operation works in a manner similar to the natural joint operation, but preserves those tuples that would be lost in and joined by creating the tuples in the result containing null values. There are actually three forms of the operation left outer joint, right outer joint and full outer joint. All three forms of the outer joint compute the joint and add extra tuples to the result of the joint. Now consider left outer joint. Left outer joint type returns all the rows from the left-hand side relations specified in the condition and only those rows from the other table where the joint fills are equal. The left outer joint would return the all records from the table 1 and only those records from the table 2 that intersect with the table 1. The left outer joint takes all the tuples in the left relation that did not match with any tuples in the right relation, pairs the tuples with the null values for all other attributes from the right-hand side relation and add them to the result of the natural joint. All the information from the left-hand side relation is present in the result of the left outer joint. We compute the left outer joint operation as follows. First, compute the result of the inner joint, then for every tuple t in the left-hand side relation that does not match any tuple in the right-hand side relation in the inner joint add a tuple r to the result of the joint constructed as follows. The attributes of the r that are derived from the left-hand side relation are filled in with the values from the tuple t. The remaining attribute of the r are filled with the null values. Consider the relation for the queries on the left-outer joint. Relation loan with the attribute loan number branch name and the amount. Relation borrower with the attribute customer name and the loan number. In both relation common attribute is loan number and here borrower information is missing for L 260 in the borrower relation and loan information is missing for L 155 in the loan relation. Consider the left-outer joint using the natural joint condition. The meaning of the joint condition natural in terms of which tuples from the two relation match is straight forward. The ordering of the attribute in the result of the natural joint is as follows. The joint attribute that is the attribute common to both relation appear first in the order in which they appear in the left-hand side relation. Then next come all the non-joint attribute of the left-hand side relation and finally all non-joint attributes of the right-hand side relation. Relation loan and borrower the query select star from loan natural left-outer joint borrower. The left-outer joint preserves tuples only in the relation name before the left-outer joint operation. In the result the tuple for L 260 includes the null for the attribute that appear only in the schema of the borrower relation that is customer name. Consider the left-outer joint using on-joint condition. The on condition allows a general predicate over the relation being joint. Loan and the borrower relation query is select star from the loan left-outer joint borrower on loan dot loan number is equal to borrower dot loan number. On and where behaves differently for the outer joint. The reason for this is that outer joint adds null padded tuples only for those tuples that do not contribute to the result of the corresponding inner joint. The on condition is a part of the outer joint specification but a where clause is this note. Here in the result the common attribute loan number appear twice and the tuple for L 260 includes null for attribute that appear only in the schema of the borrower relation that is customer name and the loan number. Left-outer joint using using joint condition. The using clause is a form of natural joint that only requires the values to match on the specified attributes. The relation loan and borrower query for this left-outer joint using the using condition is select star from loan left-outer joint borrower using loan number. The result of this query is shown on the slide. Here the common attribute loan number appears only once till now we consider the left-outer joint using joint condition natural on and using. Now pause this video and reflect on this question for a minute or two minutes and write your response. Once you return the answer to this question then you can restart playing this video. The question is write the queries for left-outer joint using natural on and using condition along with the result. The employee relation with the attribute employee name Stee and the city and the employee salary relation with the employee name branch name and the salary. The employee salary relation with the employee name branch name and the salary so it is given on this slide. Here the salary information is missing for the employee smith in the employee salary relation and the employee information is missing for the employee get in the employee relation. I hope all of you have completed this activity. Consider the falling relation employee and the employee salary. First consider the left-outer joint using the natural condition. The query is select star from EMP natural left-outer joint EMP salary. The result is shown in the table. In the result the common attribute employee name appear only once and the tuple for smith includes the null value for the attribute that appear only in the schema of the employee salary relation that that is branch name and the salary. Now consider the left-outer joint using on condition for the falling relations. The query is select star from EMP left-outer joint EMP salary on EMP dot employee name equals to EMP salary dot employee name. The result is shown on the slide which contains the common attribute employee name twice. Now consider the left-outer joint using using condition for the falling relation. The query is select star from the EMP left-outer joint EMP salary using employee name. The result contain the common attribute employee name only once. Here on this slide query is for left-outer joint using natural on and using conditions along with the results are given. The result using joint condition natural and using are same in which the common attribute employee name appear only once. In joint condition on the common attribute employee name appears twice. These are the references used for preparing this presentation. Thank you.