Example 1: Display a list of all students, their ID, name and dept_name and tot_cred along with the courses they have taken

select *
from student natural left outer join takes

The query does NOT work. Since natural join drops any tuple that does not match with the second relation’s tuple. The students with no courses will not get matched, therefore would not appear in the result.

Example 2: Find all students who have not taken a course

select ID
from student natural left outer join takes
where course_id is null

There it will output Snow, since he doesn’t take any courses, course id is null since all attributes of tuple $r$ are filled with null values.

Example 3: right outer join of takes and student relations

select *
from takes natural right outer join student

• First few attributes come from takes then from student
• Since it’s right outer join, it preserves tuples from student (which is the right in our case) and pad with null all other attributes.

Example 4: full outer join

Related

• SQL Joins
• Structured Query Language