JOINS

1

• JOINS

2
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.

3
Consider these tables
4
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 Pitts 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

5
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.

6
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 dont qualify the column, will received
  ambiguous column error

7
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?

8
Outer Joins
9
Outer joins students without faculty

• List the students first and last name and the
  advisors 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
10
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
11
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

12
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
13
ANSI Standard Joins

• SELECT lttable1.column1,table2.column,gt
• FROM lttable1gt
• INNER JOIN lttable2gt ON lttable1.columngt
  lttable2.columngt
• SELECT sfname, slname, flname, grade, csecid
• FROM student
• INNER JOIN faculty ON faculty.fid student.fid
• INNER JOIN enrollment ON enrollment.sid
  student.sid

14
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'