Chapter 4 Intermediate SQL Joins Database System Concepts

Chapter 4: Intermediate SQL Joins Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www. db-book. com for conditions on re-use

Chapter 4: Intermediate SQL n Join Expressions n Views n Transactions n Integrity Constraints n SQL Data Types and Schemas n Authorization

Joined Relations n Join operations take two relations and return as a result another relation. n 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 n The join operations are typically used as subquery expressions in the from clause

Types of Joins n [Inner] Join n [Natural] Outer Joins l [Natural] Left Outer Join l [Natural] Right Outer Join l [Natural] Full Outer Join

Recall Join - Using n select name, title from (instructor natural join teaches) join course using(course_id);

Join operations – Example n Relation course n Relation prereq n Observe that prereq information is missing for CS-315 and course information is missing for CS-437

Outer Join n An extension of the join operation that avoids loss of information. n 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. n Uses null values. n Example query l Find all courses without a prerequisite.

Left Outer Join n course natural left outer join prereq n select course_id from course natural left outer join prereq where prereq_id is null

Right Outer Join n course natural right outer join prereq

Joined Relations n Join operations take two relations and return as a result another relation. n These additional operations are typically used as subquery expressions in the from clause n Join condition – defines which tuples in the two relations match, and what attributes are present in the result of the join. n 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 n course natural full outer join prereq

Joined Relations – Examples n course inner join prereq on course_id = prereq. course_id n What is the difference between the above, and a natural join? n course left outer join prereq on course_id = prereq. course_id

Joined Relations – Examples n course natural right outer join prereq n course full outer join prereq using (course_id)