SQL Join

SQL supports joins to produce merged tables/views using the JOIN keyword. The keyword is used after the SELECT clause (specifically, after FROM)

Inner Join (Equi-Join) §

SELECT * FROM [A] INNER JOIN [B]
	ON [equalities];

Natural Join §

SELECT * FROM [A] NATURAL JOIN [B];

Note that no condition is needed, as natural join automatically tries to find like attributes.

%%🖋 Edit in Excalidraw, and the dark exported image%%

Outer Join §

• An extension of inner join / equi-join that also adds records from either table $A$ or $B$
• If it is a LEFT outer join, it takes the left table ($A$) and:
  • For any record that satisfies the inequality, treat it as it would be treated in an inner join
  • Any other record has NULL values for attributes belonging to $B$. See example
• If it is a RIGHT outer join, takes the right table ($B$) and:
  • For any record that satisfies the inequality, treat it as it would be treated in an inner join
  • Any other record has NULL values for attributes belonging to $A$. See example:
• A FULL outer join merges any records where equalities holds, but adds records from $A$ (where attributes in $B$ are NULL) AND $B$ (where attributes from $A$ are NULL)

%%🖋 Edit in Excalidraw, and the dark exported image%%

Properties of Joins §

• Commutative: $A\bowtie B=B\bowtie A$
• Associative: $A\bowtie (B\bowtie C)=(A\bowtie B)\bowtie C$