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:

idstart_pairend_pair
108:30:0009:15:00
209:20:0010:05:00
310:15:0011:00:00
411:05:0011:50:00
512:50:0013:35:00
613:40:0014:25:00
714:35:0015:20:00
815:25:0016:10:00

Data in the Schedule table

iddateclassnumber_pairteachersubjectclassroom
12019-09-01T00:00:00.000Z9111147
22019-09-01T00:00:00.000Z928213
32019-09-01T00:00:00.000Z934313
42019-09-02T00:00:00.000Z914313
52019-09-02T00:00:00.000Z922434
62019-09-02T00:00:00.000Z936535
72019-09-03T00:00:00.000Z915636
82019-09-03T00:00:00.000Z9213737
92019-09-03T00:00:00.000Z936838
102019-09-04T00:00:00.000Z919939
112019-09-04T00:00:00.000Z92101040
122019-09-04T00:00:00.000Z9331141
132019-09-05T00:00:00.000Z9131343
142019-09-05T00:00:00.000Z9211147
152019-09-05T00:00:00.000Z935636
162019-08-30T00:00:00.000Z912434
172019-08-30T00:00:00.000Z928213
182019-08-30T00:00:00.000Z936535
192019-08-30T00:00:00.000Z9410147
202019-09-03T00:00:00.000Z94101040
212019-08-30T00:00:00.000Z817953
222019-08-30T00:00:00.000Z827953
232019-08-30T00:00:00.000Z838238
242019-08-30T00:00:00.000Z8411143
252019-08-30T00:00:00.000Z858339
262019-09-01T00:00:00.000Z822434
272019-09-01T00:00:00.000Z836535
282019-09-01T00:00:00.000Z8412636
292019-09-01T00:00:00.000Z8513737
302019-09-02T00:00:00.000Z836838
312019-09-02T00:00:00.000Z847953
322019-09-03T00:00:00.000Z81101040
332019-09-03T00:00:00.000Z827953
342019-09-03T00:00:00.000Z837953
352019-09-04T00:00:00.000Z811114
362019-09-04T00:00:00.000Z8211242
372019-09-04T00:00:00.000Z8331343
382019-09-04T00:00:00.000Z848242
392019-09-04T00:00:00.000Z8511143
402019-09-05T00:00:00.000Z8211143
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;
timepair.idstart_pairend_pairschedule.iddateclassnumber_pairteachersubjectclassroom
108:30:0009:15:00352019-09-04T00:00:00.000Z811114
108:30:0009:15:00322019-09-03T00:00:00.000Z81101040
108:30:0009:15:00212019-08-30T00:00:00.000Z817953
108:30:0009:15:00162019-08-30T00:00:00.000Z912434
108:30:0009:15:00132019-09-05T00:00:00.000Z9131343
108:30:0009:15:00102019-09-04T00:00:00.000Z919939
108:30:0009:15:0072019-09-03T00:00:00.000Z915636
108:30:0009:15:0042019-09-02T00:00:00.000Z914313
108:30:0009:15:0012019-09-01T00:00:00.000Z9111147
209:20:0010:05:00402019-09-05T00:00:00.000Z8211143
209:20:0010:05:00362019-09-04T00:00:00.000Z8211242
209:20:0010:05:00332019-09-03T00:00:00.000Z827953
209:20:0010:05:00262019-09-01T00:00:00.000Z822434
209:20:0010:05:00222019-08-30T00:00:00.000Z827953
209:20:0010:05:00172019-08-30T00:00:00.000Z928213
209:20:0010:05:00142019-09-05T00:00:00.000Z9211147
209:20:0010:05:00112019-09-04T00:00:00.000Z92101040
209:20:0010:05:0082019-09-03T00:00:00.000Z9213737
209:20:0010:05:0052019-09-02T00:00:00.000Z922434
209:20:0010:05:0022019-09-01T00:00:00.000Z928213
310:15:0011:00:00372019-09-04T00:00:00.000Z8331343
310:15:0011:00:00342019-09-03T00:00:00.000Z837953
310:15:0011:00:00302019-09-02T00:00:00.000Z836838
310:15:0011:00:00272019-09-01T00:00:00.000Z836535
310:15:0011:00:00232019-08-30T00:00:00.000Z838238
310:15:0011:00:00182019-08-30T00:00:00.000Z936535
310:15:0011:00:00152019-09-05T00:00:00.000Z935636
310:15:0011:00:00122019-09-04T00:00:00.000Z9331141
310:15:0011:00:0092019-09-03T00:00:00.000Z936838
310:15:0011:00:0062019-09-02T00:00:00.000Z936535
310:15:0011:00:0032019-09-01T00:00:00.000Z934313
411:05:0011:50:00382019-09-04T00:00:00.000Z848242
411:05:0011:50:00312019-09-02T00:00:00.000Z847953
411:05:0011:50:00282019-09-01T00:00:00.000Z8412636
411:05:0011:50:00242019-08-30T00:00:00.000Z8411143
411:05:0011:50:00202019-09-03T00:00:00.000Z94101040
411:05:0011:50:00192019-08-30T00:00:00.000Z9410147
512:50:0013:35:00392019-09-04T00:00:00.000Z8511143
512:50:0013:35:00292019-09-01T00:00:00.000Z8513737
512:50:0013:35:00252019-08-30T00:00:00.000Z858339
613:40:0014:25:00<NULL><NULL><NULL><NULL><NULL><NULL><NULL>
714:35:0015:20:00<NULL><NULL><NULL><NULL><NULL><NULL><NULL>
815:25:0016:10:00<NULL><NULL><NULL><NULL><NULL><NULL><NULL>

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:

SchemaJOIN 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

We are in touch with you
English