 Hello everyone, I am Mrs. Sunita Doher. The topic covered is joint relation inner joint. SQL provides not only the basic Cartesian product mechanism for joining the tuples of the relation, but SQL also provides various other mechanism for joining the relation including condition joints and the natural joints as well as various forms of the outer joint. These additional operations are typically used as subquery expression in the from clause. Learning outcome, at the end of the session students will be able to know about the joint operation and write the query using inner joint operation for given relation. In addition to the natural joint, SQL provides other forms of the joint operation including the ability to specify an explicit joint predicate and the ability to include in the result tuples that are excluded by the natural joint. Joint operations take two relations and return as a result another relation. These joint operations are typically used as a subquery expression in the from clause. Each of the variants of the joint operation in SQL consist of a joint type and the joint condition. Joint condition defines which tuple in two relation match and what attribute are present in the result of the joint. Joint type defines how tuples in each relation that do not match any tuple in the other relation are treated. Tuple shows some of the allowed joint types and the joint condition. The first joint type is the inner joint and the other three are the outer joints. There are three joint conditions that is the natural on and the using. The use of a joint condition is mandatory for the outer joint but is optional for the inner joint. In inner joint if the joint condition is omitted a Cartesian product result. Syntactically the keyword natural appears before the joint type whereas the on and the using condition appear at the end of the joint expression. The keyword inner and the outer are optional since the rest of the joint type deduce whether the joint is an inner joint or an outer joint. The meaning of the joint condition natural in terms of which tuple from the two relation match is straight forward. The order of the attribute in the result of the natural joint is as follows. The joint attribute that is the attribute common to both the relation appear first in the order in which they appear in the left hand side relation. Next come all the non joint attribute of the left hand side relation and finally all non joint attribute of the right hand side relation. The using clause is a form of natural joint that only requires values to be values to match on the specified attribute. The on condition allows a general predicate over the relation being joined. Like the using condition the on condition appears at the end of the joint expression. Consider inner joint. In this visual diagram the inner joint returns the shaded area. The inner joint would return the records where table 1 and table 2 intersect. Normal joints are called inner joints in the SQL. The keyword inner is however optional. The default joint type is the inner joint. Consider the relation for the queries on the inner joint. Relation loan with the loan number branch name and the amount. Relation borrower with the attribute customer name and loan number. In both relation the common attribute is loan number. 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 first the natural joint relations loan and borrower. The query is select star from loan natural joint borrower. The natural joint operation requires that for a loan tuple and a borrower tuple to match. Result is given on the slide. The attribute of the result consists of the attribute of the left hand side relation followed by the attributes of the right hand side relation. Hence the result contains loan number branch name and amount attribute of the left hand side relation loan and the customer attribute of the right hand side relation borrower. Now consider the inner joint using the natural joint condition. So relations loan and borrower. The query for the inner joint using natural joint condition is select star from loan natural inner joint borrower. This expression computes the natural joint of two relations. The only attribute name common to the loan and borrower is the loan number. The result is shown on the slide. The natural joint is equivalent to the natural inner joint. Hence the result of the natural joint and the natural inner joint is same. Consider the inner joint using on joint condition. So the relation loan and the borrower query is select star from loan inner joint borrower on loan dot loan number equals to borrower dot loan number. The on condition above specifies that a tuple from loan matches a tuple from the borrower if their loan number values are equal. The result has the loan number attribute listed twice in the joint result. Once for the loan and once for the borrower even though their loan number values must be the same. The SQL standard does not require attribute name in such result to be unique. The ask clause should be used to assign the unique name to the attribute in the query and the sub query result. We can rename the second occurrence of loan number to the customer loan number. The ordering of the attribute in the result of the joint is important for renaming. Consider the inner joint using using joint condition relation loan and borrower. The query is select star from loan inner joint borrower using loan number. The result is shown on this slide. The result has loan number attribute listed only once in the joint result. Till now we consider the inner joint using joint condition natural on and the using. Now pause this video and reflect on this question for a minute or two minute 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 the inner joint using natural on and using condition along with the result. For this question consider two relations the employee relation with the attribute employee name street and the city and the employee salary relation with the employee name branch name and the salary. Here salary information is missing for the employee smith in the employee salary relation and the employee information is missing for the gate employee in the employee relation. I hope all of you have completed this activity. Consider the following relations employees and employee salary. So, first consider the inner joint using natural condition. The query is select star from EMP natural inner joint EMP salary. The result is shown in the table. The common attribute employee name appears only once. Now consider the inner joint using on condition for the following relations. The query is select star from EMP inner joint EMP salary on EMP dot employee name is equal to EMP salary dot employee name. The result is shown on the slide which contains the common attribute employee name twice. Now consider the inner joint using using condition for the following relation. The query is select star from EMP inner joint EMP salary using in parenthesis employee name. Results contain the common attribute employee name only once. Here on the slide queries for inner joint using natural on and using condition along with the results are given. The results using the joint condition natural and using are same in which the common attribute employee name appears only once. In joint condition on the common attribute employee name appears twice. These are the references used for preparing this presentation. Thank you.