A product followed by a selection is so common that it has its own symbol, ⋈.
Theta join
text
r ⋈ θ s = σ θ (r × s)θ (theta) is any condition. Textbooks write it as a subscript under ⋈. In SQL, θ goes after ON:
sql
SELECT * FROM instructor JOIN department ON instructor.salary > department.budget;When θ uses only =, the join is an equijoin.
Natural join
r ⋈ s with no condition is the . It:
- finds the attributes that have the same name in
rands, - keeps the pairs that are equal on all of them,
- keeps one copy of each common attribute.
instructorid, name, dept_name, salary
common: id
teachesid, course_id, semester, year
result
id, name, dept_name, salary, course_id, semester, year7 attributes, id once
In SQL: instructor NATURAL JOIN teaches, or JOIN teaches USING (id) (Chapter 8).
The natural join matches every common name, also the ones you did not mean. instructor and course share dept_name. So instructor ⋈ course pairs each instructor with every course of the same department, not with the courses the instructor teaches. If two relations have no common attribute, the natural join is the product.
Outer joins
Textbooks also use symbols for the outer joins of Chapter 8:
| Algebra | SQL |
|---|---|
r ⟕ s | r LEFT JOIN s |
r ⟖ s | r RIGHT JOIN s |
r ⟗ s | r FULL JOIN s |