Intermediate SQL
1/18/2021 1
1/18/2021 2
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
1/18/2021 3
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;
1/18/2021 4
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 ;
1/18/2021 5
Student Relation
1/18/2021 6
Takes Relation
1/18/2021 7
student natural join takes
1/18/2021 8
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.
1/18/2021 9
Two-tier and three-tier architectures
1/18/2021 10
