Transcript Joins and Lab 4

JOINS
Equi Join – Inner Join
 Rules for joins:
 the attributes must have the same data type.
 the attributes must be of the same size.
 the attributes do NOT need to have the same
attribute name.
2
Consider these tables
3
Equi Join – Inner Joins
 The join condition specifies attributes which are common




between two tables.
 foreign key reference in one table = corresponding
primary key in other table.
Brad Pitt’s faculty advisor (fid) = 2.
Fid 2 in the faculty table = Meryl Streep.
The value of Student.fid is equal to value of Faculty.FID.
When we write a join, we are literally saying to SQL that
the value of one attribute is equal to the value of another
attribute (foreign key = primary key).
faculty.fid = student.fid
4
Rules on Joins
 No limit to number of tables that can be
added to a query.
 To add a table to be the query:
 Add the table to the FROM clause.
 Remember to separate table names with commas.
 Add join to the WHERE clause.
 Separate the joins with an AND.
 Rule of Thumb -1 less join than there are
tables.
5
Qualifying Columns
 Tablename.attribute notation must be used if
attribute name not unique in the tables in the query
 i.e. if you have fname in the student and the faculty
tables and both called in the query, must specify
which table the attribute is in
 Select student.fname, student.lname, gpa
from student, faculty
where student.fid = faculty.fid;
 If don’t qualify the column, will received ambiguous
column error
6
Outer Joins
 In an Inner Join, looking for records that match.
 What if you want to find records in one table
without a match in the other?
7
Outer Joins
8
Outer joins – students
without faculty
 List the student’s first and last name and
the advisor’s first and last name
regardless if the student has a faculty
advisor.
SELECT sfname, slname, ffname, flname
FROM student, faculty
WHERE student.fid = faculty.fid(+);
(+) shows it is optional
9
Outer joins – Faculty without
Students
Show faculty members and their associated
students.
SELECT ffname, flname, sfname, slname
FROM student, faculty
WHERE student.fid(+) = faculty.fid;
10
Alias
 Allows you to add an alias or new
column heading. Applies only to
that query
 SELECT sfname AS "Student First
Name", slname AS "Student Last
Name"
FROM student;
11
Computed Columns
 New columns can be created through valid
computations or formulas
 Formulas may contain mathematical operators
 May contain attributes of any tables specified in
FROM clause
Select temperature as “Original Temperature”,
temperature * 1.5 as “Adjusted Temperature”
From vet_appt
Where vet_appt_type = 1;
12
ANSI Standard Joins
 SELECT <table1.column1,…table2.column,…>
 FROM <table1>
 INNER JOIN <table2> ON <table1.column> = <table2.column>
…;
SELECT sfname, slname, flname, grade, csecid
FROM student
INNER JOIN faculty ON faculty.fid = student.fid
INNER JOIN enrollment ON enrollment.sid = student.sid;
13
ANSI Standard Joins
Traditional:
SELECT sfname, slname, flname, grade, csecid
FROM student, faculty, enrollment
WHERE faculty.fid = student.fid AND
enrollment.sid = student.sid AND
student.sid = enrollment.sid AND
grade = 'A';
ANSI Standard:
SELECT sfname, slname, flname, grade, csecid
FROM student
INNER JOIN faculty ON faculty.fid = student.fid
INNER JOIN enrollment ON enrollment.sid = student.sid
Where grade = 'A';
14