Outer Join

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).

LEFT OUTER JOIN

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

MySQL
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.

RIGHT OUTER JOIN

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.

FULL OUTER JOIN

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

Basic queries for different types of table joins:

Schema	JOIN query
	Retrieving all data from the left table joined with corresponding data from the right table: MySQL SELECT table_fields FROM left_table LEFT JOIN right_table ON right_table.key = left_table.key
	Retrieving all data from the right table joined with corresponding data from the left table: MySQL SELECT table_fields FROM left_table RIGHT JOIN right_table ON right_table.key = left_table.key
	Retrieving data that only belongs to the left table: MySQL SELECT table_fields FROM left_table LEFT JOIN right_table ON right_table.key = left_table.key WHERE right_table.key IS NULL
	Retrieving data that only belongs to the right table: MySQL SELECT table_fields FROM left_table RIGHT JOIN right_table ON right_table.key = left_table.key WHERE left_table.key IS NULL
	Retrieving data that belongs to both the left and right tables: MySQL SELECT table_fields FROM left_table INNER JOIN right_table ON right_table.key = left_table.key
	Retrieving all data that belongs to both the left and right tables, as well as their inner join: MySQL SELECT table_fields FROM left_table FULL OUTER JOIN right_table ON right_table.key = left_table.key
	Retrieving data that does not belong to both the left and right tables simultaneously (reverse INNER JOIN): MySQL SELECT table_fields FROM left_table FULL OUTER JOIN right_table ON right_table.key = left_table.key WHERE left_table.key IS NULL OR right_table.key IS NULL