An outer join can be of three types: (LEFT), (RIGHT), and (FULL). By default, it is full.
The main difference between an outer join and an inner join is that it must return all rows of one (LEFT, RIGHT) or both tables (FULL).
A join that returns all values from the left table joined with corresponding values from the right table if they satisfy the join condition, or replaces them with NULL otherwise.
For example, let's get the schedule of classes from a database, joined with the corresponding time pairs in the schedule.
Data in the Timepair table:
Data in the Schedule table
SELECT Timepair.id 'timepair.id', start_pair, end_pair, Schedule.id 'schedule.id', date, class, number_pair, teacher, subject, classroom FROM Timepair LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id;
The selection includes all rows from the left table, supplemented with data about classes. Note that there are rows at the end of the table with fields filled with NULL. These are the rows for which corresponding classes were not found, but they are present in the left table, so they were also output.
A join that returns all values from the right table joined with corresponding values from the left table if they satisfy the join condition, or replaces them with NULL otherwise.
A join that performs an inner join of records and supplements them with a left outer join and a right outer join.
The algorithm of the full join:
- A table is formed based on the inner join.
- Values that did not enter the result of the formation from the left table are added to the table
- Values that did not enter the result of the formation from the right table are added to the table
Full join is not implemented in all DBMS. For example, it is not present in MySQL, but it can be easily emulated:MySQL
SELECT * FROM left_table LEFT JOIN right_table ON right_table.key = left_table.key UNION ALL SELECT * FROM left_table RIGHT JOIN right_table ON right_table.key = left_table.key WHERE left_table.key IS NULL