Summary
Highlights
A Full Outer Join combines the results of Left and Right Joins. It returns all records when there is a match in either the left or right table, essentially providing a union of all matching and non-matching records from both tables. The 'OUTER' keyword is optional.
A Cross Join, also known as a Cartesian Join, returns the Cartesian product of the rows from the joined tables. This means every row from the first table is combined with every row from the second table. It does not require an ON condition, as it inherently matches all possible combinations. The number of records returned is the product of the number of records in each table.
A Natural Join implicitly joins two tables based on all columns that have the same name in both tables. It automatically figures out the join condition, which can be problematic if column names are shared but the data within those columns is not intended for joining. If no common columns are found, it behaves like a Cross Join. It is generally not recommended for real-world scenarios due to its unpredictable behavior.
A Self Join is used to join a table with itself. This is useful when you need to compare rows within the same table, such as finding a parent-child relationship in a single table. It requires the use of table aliases to distinguish between the two instances of the table being joined. Any type of join (INNER, LEFT, RIGHT, FULL) can be used for a self-join.