1 / 10

Joined Relations

Joined Relations. Conception of outer join. Differences between outer join and general join. Ex am pl e of LEFT OUTER JOIN and RIGTHR OUTER JOIN. Introduction of comp lex condition join. Conceptions.

rozene
Download Presentation

Joined Relations

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. Joined Relations Conception of outer join. Differences between outer join and general join. Example of LEFT OUTER JOIN and RIGTHR OUTER JOIN. Introduction of complex condition join.

  2. Conceptions OUTER JOIN: A abandon of the tuple also kept in the outcome of the group relations, and other attributes to fill in the blank value with NULL. LEFT OUTRE JOIN： listed all the tuple on the left side of the relationship. RIGHT OUTER JOIN: listed all the tuple on the right side of the relationship

  3. outer join Differences between outer join and ordinary join. ordinary join operation only output the tuple that meet the connection conditions of it. outer join operation designated for the main relation table,and output the tuple togeter though it doesn't meet the connection condition. [例 36] 改写[例33] SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno)； An Introduction to Database System

  4. outer join(2) 执行结果： An Introduction to Database System

  5. Examples Relation loan customer-name loan-number loan-number branch-name amount Jones Smith Hayes L-170 L-230 L-260 L-170 L-230 L-155 Downtown Redwood Perryridge 3000 4000 1700 • Relation borrower • Note: borrower information missing for L-260 and loan information missing for L-155

  6. The Result of loan left outer join borrower on loan-number

  7. The Result of loan natural right outer join borrower

  8. The Result of loan full outer join borrower using(loan-number)

  9. Complex condition join what is the complex condition join? when it have more connection condition in the sentence WHERE. [e.37]Query for all the students who select NO.2 elective courses and results in more than 90 hours. SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND /* 连接谓词*/ SC.Cno=‘2’AND SC.Grade>90;/*其他限定条件 */ An Introduction to Database System

  10. complex condition join 2 In addition to the operation of connecting the two tables can be connected to a table with their own , can also connect tables for two or more , the latter we often referre it as the table even more. [e38] SELECT Student.Sno，Sname，Cname，Grade FROM Student，SC，Course /*多表连接*/ WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno； An Introduction to Database System

More Related