SQL contd.
• Joins
Joined Relations
• Join operations take two relations and return as a result another relation.
• A join operation is a Cartesian product which requires that tuples in the two
relations match (under some condition). It also specifies the attributes that are
present in the result of the join
• The join operations are typically used as subquery expressions in the from clause
• Three types of joins:
• Natural join
• Inner join
• Outer join
Natural Join in SQL
• Natural join matches tuples with the same values for all common attributes, and
retains only one copy of each common column.
• List the names of instructors along with the course ID of the courses that they
taught
• select name, course_id
from students, takes
where [Link] = [Link];
• Same query in SQL with “natural join” construct
• select name, course_id
from student natural join takes;
Natural Join in SQL (Cont.)
• The from clause can have multiple relations combined using natural join:
select A1, A2, … An
from r1 natural join r2 natural join .. natural join rn
where P ;
Student Relation
Takes Relation
student natural join takes
Dangerous in Natural Join
• Beware of unrelated attributes with same name which get equated incorrectly
• Example -- List the names of students instructors along with the titles of courses
that they have taken
• Correct version
select name, title
from student natural join takes, course
where takes.course_id = course.course_id;
• Incorrect version
select name, title
from student natural join takes natural join course;
• This query omits all (student name, course title) pairs where the
student takes a course in a department other than the student's own
department.
• The correct version (above), correctly outputs such pairs.
Outer Join
• An extension of the join operation that avoids loss of information.
• Computes the join and then adds tuples form one relation that does not match
tuples in the other relation to the result of the join.
• Uses null values.
• Three forms of outer join:
• left outer join
• right outer join
• full outer join
Outer Join Examples
• Relation course
• Relation prereq
• Observe that
course information is missing CS-347
prereq information is missing CS-315
Left Outer Join
• course natural left outer join prereq
In relational algebra: course ⟕ prereq
Right Outer Join
• course natural right outer join prereq
In relational algebra: course ⟖ prereq
Full Outer Join
• course natural full outer join prereq
• In relational algebra: course ⟗ prereq
Joined Types and Conditions
• Join operations take two relations and return as a result another relation.
• These additional operations are typically used as subquery expressions in the
from clause
• Join condition – defines which tuples in the two relations match.
• Join type – defines how tuples in each relation that do not match any tuple in
the other relation (based on the join condition) are treated.
Joined Relations – Examples
• course natural right outer join prereq
• course full outer join prereq using (course_id)
Joined Relations – Examples
• course inner join prereq on
course.course_id = prereq.course_id
• What is the difference between the above, and a natural join?
• course left outer join prereq on
course.course_id = prereq.course_id
Joined Relations – Examples
• course natural right outer join prereq
• course full outer join prereq using (course_id)