SQL Joins

Super important topic of Structured Query Language.

There are different types of joins:

• Inner join: Returns records that have matching values in both tables
• left (outer) join: Returns all records from the left table, and the matched records from the right table
• right (outer) join: Returns all records from the right table, and the matched records from the left table
• full (outer) join: Returns all records when there is a match in either left or right table

Inner Join

Do not preserve unmatched tuples.

Natural Join

The natural join operations operates on two relation and produces an output as a result. It considers only those pairs of tuples with the same value on those attributes that appear in the schemas for both relations.

student natural join takes

• considers only the pairs of tuples where both the tuple from student and the tuple from takes have the same value on the common attribute, that is ID.

Order of how the relation is outputted: first attribute is the common to both relations, second those attributes unique to the first relation, those attributes unique to the second relation.

Join on

https://www.geeksforgeeks.org/difference-between-natural-join-and-inner-join-in-sql/

The on condition allows a general condition over the relations being joined.

select *
from student join takes on student.ID=takes.ID

• The on condition specifies that a tuple from student matches a tuple from takes if their ID values are equal.
• Same as natural join.
• The one difference is that the result has the ID attribute listed twice in the join result, since once for student and once for takes even if they have the same ID values.

Since the on condition can express any SQL predicate, thus join expression using the on condition can express richer class of join conditions than natural join.

Why use the on condition if it’s similar to where clause?

• useful in outer join
• more readable to have the on clause in the join condition

Outer Join

The outer-join operation preserves those tuples that would be lost in a join by creating tuples in the result containing null values.

Very good example in the textbook at page 131 that illustrates the power of outer join. Outer Join Examples

Three forms of outer join:

• left outer join: preserves tuples only in the relation named before
• right outer join: preserves tuples only in the relation named after
• full outer join: preserves tuples in both relations.