《数据库系统概念 Database System Concepts》原书教学资源（第六版，PPT课件讲稿，英文版）Chapter 4 Intermediate SQL

Chapter 4:Intermediate SQL Join Expressions Views Transactions Integrity Constraints SQL Data Types and Schemas Authorization Database System Concepts-6th Edition 4.2 ©Silberschat乜，Korth and Sudarshan

Database System Concepts - 6 4.2 ©Silberschatz, Korth and Sudarshan th Edition Chapter 4: Intermediate SQL Join Expressions Views Transactions Integrity Constraints SQL Data Types and Schemas Authorization

Joined Relations Join operations take two relations and return as a result another relation. A join operation is a Cartesian product which requires that tuples in the two relations match (under some condition). It also specifies the attributes that are present in the result of the join The join operations are typically used as subquery expressions in the from clause Database System Concepts-6th Edition 4.3 @Silberschatz,Korth and Sudarshan

Database System Concepts - 6 4.3 ©Silberschatz, Korth and Sudarshan th Edition Joined Relations Join operations take two relations and return as a result another relation. A join operation is a Cartesian product which requires that tuples in the two relations match (under some condition). It also specifies the attributes that are present in the result of the join The join operations are typically used as subquery expressions in the from clause

Join operations -Example Relation course course id title dept name credits BIO-301 Genetics Biology 4 CS-190 Game Design Comp.Sci. 4 CS-315 Robotics Comp.Sci. 3 Relation prereg course id prereg id BIO-301 BIO-101 CS-190 CS-101 CS-347 CS-101 Observe that prereq information is missing for CS-315 and course information is missing for CS-437 Database System Concepts-6th Edition 4.4 ©Silberschat乜，Korth and Sudarshan

Database System Concepts - 6 4.4 ©Silberschatz, Korth and Sudarshan th Edition Join operations – Example Relation course Relation prereq Observe that prereq information is missing for CS-315 and course information is missing for CS-437

Outer Join An extension of the join operation that avoids loss of information. Computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join. Uses null values. Database System Concepts-6th Edition 4.5 @Silberschatz,Korth and Sudarshan

Database System Concepts - 6 4.5 ©Silberschatz, Korth and Sudarshan th Edition Outer Join An extension of the join operation that avoids loss of information. Computes the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join. Uses null values

Left Outer Join course natural left outer join prereg course id title dept name credits prereq id BIO-301 Genetics Biology 4 BIO-101 CS-190 Game Design Comp.Sci. 4 CS-101 CS-315 Robotics Comp.Sci. 3 null Database System Concepts-6th Edition 4.6 ©Silberschat乜，Korth and Sudarshan

Database System Concepts - 6 4.6 ©Silberschatz, Korth and Sudarshan th Edition Left Outer Join course natural left outer join prereq

Right Outer Join course natural right outer join prereg course id title dept name credits prereq id BI0-301 Genetics Biology 4 BIO-101 CS-190 Game Design Comp.Sci. 4 CS-101 CS-347 null null null CS-101 Database System Concepts-6th Edition 4.7 ©Silberschat乜，Korth and Sudarshan

Database System Concepts - 6 4.7 ©Silberschatz, Korth and Sudarshan th Edition Right Outer Join course natural right outer join prereq

Joined Relations Join operations take two relations and return as a result another relation. These additional operations are typically used as subquery expressions in the from clause Join condition-defines which tuples in the two relations match,and what attributes are present in the result of the join. Join type-defines how tuples in each relation that do not match any tuple in the other relation(based on the join condition)are treated. Join types Join Conditions inner join natural left outer join on right outer join using (A1,A1,...,An) full outer join Database System Concepts-6th Edition 4.8 ©Silberschat乜，Korth and Sudarshan

Database System Concepts - 6 4.8 ©Silberschatz, Korth and Sudarshan th Edition Joined Relations Join operations take two relations and return as a result another relation. These additional operations are typically used as subquery expressions in the from clause Join condition – defines which tuples in the two relations match, and what attributes are present in the result of the join. Join type – defines how tuples in each relation that do not match any tuple in the other relation (based on the join condition) are treated

Full Outer Join course natural full outer join prereg course_id title dept name credits prereq id BI○-301 Genetics Biology 4 BIO-101 CS-190 Game Design Comp.Sci. 4 CS-101 CS-315 Robotics Comp.Sci. 3 null CS-347 null null null CS-101 Database System Concepts-6th Edition 4.9 ©Silberschat乜，Korth and Sudarshan

Database System Concepts - 6 4.9 ©Silberschatz, Korth and Sudarshan th Edition Full Outer Join course natural full outer join prereq

Joined Relations-Examples course inner join prereg on course.course id prereg.course id course id title dept name credits prereq id course_id BIO-301 Genetics Biology 4 BIO-101 BI○-301 CS-190 Game Design Comp.Sci. 4 CS-101 CS-190 What is the difference between the above,and a natural join? course left outer join prereg on course.course_id prereg.course id course id title dept name credits prereq id course id BIO-301 Genetics Biology 4 BIO-101 BIO-301 CS-190 Game Design Comp.Sci. 4 CS-101 CS-190 CS-315 Robotics Comp.Sci. 3 null null Database System Concepts-6th Edition 4.10 ©Silberschat乜，Korth and Sudarshan

Database System Concepts - 6 4.10 ©Silberschatz, Korth and Sudarshan th Edition Joined Relations – Examples course inner join prereq on course.course_id = prereq.course_id What is the difference between the above, and a natural join? course left outer join prereq on course.course_id = prereq.course_id

Joined Relations-Examples course natural right outer join prereg course id title dept name credits prereq id BIO-301 Genetics Biology 4 BIO-101 CS-190 Game Design Comp.Sci. 4 CS-101 CS-347 null null null CS-101 course full outer join prereg using (course_id) course id title dept name credits prereq id BIO-301 Genetics Biology 4 BI○-101 CS-190 Game Design Comp.Sci. 4 CS-101 CS-315 Robotics Comp.Sci. 3 null CS-347 null null null CS-101 Database System Concepts-6th Edition 4.11 ©Silberschat乜，Korth and Sudarshan

Database System Concepts - 6 4.11 ©Silberschatz, Korth and Sudarshan th Edition Joined Relations – Examples course natural right outer join prereq course full outer join prereq using (course_id)