1 / 14

JOINS

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. Consider these tables. Equi Join – Inner Joins.

jonah
Download Presentation

JOINS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  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 don’t 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 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

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

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

More Related