1 / 12

Other Joins

Other Joins. ISYS 464. Outer J oin. Records in a relation that do not have matching values are included in the result relation. Missing values are set to null. Outer Join Ex a mple. Product Table: PID Pname P1 TV P2 VCR P3 Computer P4 Tape P5 DVD TotalSales PID TotalSales P1 50

liliha
Download Presentation

Other 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. Other Joins ISYS 464

  2. Outer Join • Records in a relation that do not have matching values are included in the result relation. Missing values are set to null.

  3. Outer Join Example • Product Table: • PID Pname • P1 TV • P2 VCR • P3 Computer • P4 Tape • P5 DVD • TotalSales • PID TotalSales • P1 50 • P3 60 • P5 40 • Product Join TotalSales • Product OuterJoin Totalsales PID Pname TotalSales P1 TV 50 P2 VCR P3 Computer 60 P4 Tape P5 DVD 40

  4. Branch: BID City B1 SF B2 SM B3 SJ Full Outer Join: BID City PID B1 SF P3 B2 SM Null B3 SJ P2 Null LA P1 Right Outer Join: BID City PID B1 SF P3 B3 SJ P2 Null LA P1 Property: PID City P1 LA P2 SJ P3 SF Left Outer Join: BID City PID B1 SF P3 B2 SM Null B3 SJ P2

  5. Outer Join • SELECT s.*, f.* FROM • student s FULL JOIN faculty f ON s.fid = f.fid; • SELECT s.*, f.* FROM • student s RIGHT JOIN faculty f ON s.fid = f.fid; • SELECT s.*, f.* FROM • student s LEFT JOIN faculty f ON s.fid = f.fid;

  6. Use Outer Join to Find Record in One Table with No Related Records in a Related Table Find faculty who do not advise any student. SELECT faculty.fid,fname FROM faculty left join student on faculty.fid=student.fid WHERE sid IS Null;

  7. Outer Join with Access • Query design view: • Add two tables to join • Right click the link and select Join Properties • Option 1: natural join • Option 2: Right outer join • Option 3: Left outer join • Note: How to do full outer join with Access?

  8. Self Join • Recursive relationship: • Employee supervise Employee • Ex: Empployee: eid, eName, Sex, Salary, Hire_date, SupervisorID • Find employees’ supervisor name: • SELECT e.eid, e1.ename,s.eid as superID, s.ename as sueprname • FROM empployee e, empployee s • WHERE e.SupervisorID=s.eid;

  9. Theta JoinJoin by any condition • SELECT * FROM table1, table2 WHERE criteria;

  10. Theta Join • Example: match male employees with female employees who have higher salary: • SELECT e1.eid,e1.ename,e2.eid,e2.ename • FROM empployee e1, empployee e2 • WHERE e1.sex=‘M’ and e2.sex=‘F’ • AND e1.salary < e2.salary;

  11. Theta Join • Example: match male students with female students who have higher GPA: • select s1.sid as BID, s1.sname as BNAME, s1.gpa as BGPA, s2.sid as GID, s2.sname as GNAME, s2.gpa as GGPA • from student s1, student s2 • where s1.sex='m' and s2.sex='f' and s1.gpa<s2.gpa;

  12. How to Implement Join • Outer – Inner loop • Sort – Merge • Index on the join attributes • Faculty Join Student

More Related