Join Queries

!! Inner Join

!!! Theta Join

(:source lang=sql:)[@ SELECT * FROM student INNER JOIN class ON student.class_id = class.class_id; @]

which is equivalent to

(:source lang=sql:)[@ SELECT * FROM student, class WHERE student.class_id = class.class_id; @]

!!! Equi Join

(:source lang=sql:)[@ SELECT * FROM student INNER JOIN class USING (class_id); @]

!!! Natural Join

(:source lang=sql:)[@ SELECT * FROM student NATURAL JOIN class; @]

!! Cross Join

An explicit cross join is performed using

(:source lang=sql:)[@ SELECT * FROM student CROSS JOIN grade; @]

while an implicit cross join can be performed using

(:source lang=sql:)[@ SELECT * FROM student, grade; @]

!! Outer Joins

!!! Left Outer Join

(:source lang=sql:)[@ SELECT * FROM assignment LEFT OUTER JOIN grade ON assignment.asgm_id = grade.asgm_id; @]

!!! Right Outer Join

(:source lang=sql:)[@ SELECT * FROM assignment RIGHT OUTER JOIN grade ON assignment.asgm_id = grade.asgm_id; @]

!!! Full Outer Join

(:source lang=sql:)[@ SELECT * FROM assignment FULL OUTER JOIN grade ON assignment.asgm_id = grade.asgm_id; @]