JOINS are used to retrieve data from multiple tables. Outer joins are used to return all rows from at least one of the tables. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join.
The three forms of the outer join.
- LEFT OUTER JOIN or LEFT JOIN
- RIGHT OUTER JOIN or RIGHT JOIN
- FULL OUTER JOIN or FULL JOIN
1. LEFT OUTER JOIN:
This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal.
Visual Illustration:
In this visual diagram, the LEFT OUTER JOIN returns the shaded area:
The LEFT OUTER JOIN would return the all records from table 1 and only those records from table2 that intersect with table 1
2. RIGHT OUTER JOIN:
This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal.
Visual Illustration:
In this visual diagram, the RIGHT OUTER JOIN returns the shaded area:
The RIGHT OUTER JOIN would return the all records from table2 and only those records from table 1 that intersect with table2.
3. FULL OUTER JOIN:
This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.
Visual Illustration:
In this visual diagram, the FULL OUTER JOIN returns the shaded area:
The FULL OUTER JOIN would return the all records from both table1 and table2.
