Intermediate SQL

Join operations combine two relations to produce another relation.
A join is essentially a Cartesian product combined with a condition that tuples in the two relations must match.
It specifies which attributes are included in the resulting relation.
Typically used within the FROM clause as subquery expressions.
Three main types:
- Natural join
- Inner join
- Outer join

Matches tuples based on equality of all common attribute values.
Retains only one copy of each common column.
Example:
- To list instructor names with the course IDs they taught:
  sql select name, course_id from students, takes where student.ID = takes.ID;
- Using natural join:
  sql select name, course_id from student natural join takes;
FROM clause can combine multiple relations using natural join.
- General form:
  sql select A1, A2, … An from r1 natural join r2 natural join .. natural join rn where P ;
Caution: Be wary of unrelated attributes sharing the same name, as they may be incorrectly equated.
- Example: Listing student names with course titles requires careful construction to avoid incorrect equating of attributes.
- Correct version:
  sql select name, title from student natural join takes, course where takes.course_id = course.course_id;
- Incorrect version:
  sql select name, title from student natural join takes natural join course;
- The incorrect version omits pairs where the student takes a course outside their department.

The using construct allows explicit specification of columns to be equated, avoiding erroneous attribute matching.
Example:
sql select name, title from (student natural join takes) join course using (course_id)

Specifies a general predicate over the relations being joined, akin to a WHERE clause.
Uses the keyword ON.
Example:
sql select * from student join takes on student_ID = takes_ID
This is equivalent to:
sql select * from student , takes where student_ID = takes_ID

Extends the join operation to prevent information loss.
Performs the join and then adds tuples from one relation that don't match tuples in the other relation, using NULL values.
Three types:
- Left outer join
- Right outer join
- Full outer join