Multi-table queries, the JOIN operator

Multi-table queries

Previous articles described working with only one database table data's. In reality, it is very often necessary to make a selection from multiple tables, somehow combining them. In this article you will learn the main ways to connect tables.

General structure of a multi-table query

MySQL
SELECT table_fields
FROM table_1 
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN table_2 
    ON join_condition
[[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN table_n 
    ON join_condition]

The same structure can be rewritten as follows:

MySQL
SELECT table_fields
FROM table_1 
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN table_2[ JOIN table_n] 
    ON join_condition [AND join_condition]

In most cases, the join condition is that the table columns are equal (table_1.field = table_2.field), but you can also use other comparison operators in the same way.

The connection can be internal (INNER) or external (OUTER), while the external connection is divided into left (LEFT), right (RIGHT) and full (FULL).

INNER JOIN

By default, if no parameters are specified, JOIN is executed as INNER JOIN, that is, as an internal (cross) connection of tables.

An inner join is a join of two tables where each record in the first table joining with each record from second table, creating every possible combination from both records (cartesian product).

For example, combine the tables of purchases (Payments) and family members (FamilyMembers) in such a way that each purchase is supplemented with data about who made it.

Data in Payments table:

payment_iddatefamily_membergoodamountunit_price
12005-02-12 00:00:001112000
22005-03-23 00:00:002112100
32005-05-14 00:00:0034520
42005-07-22 00:00:00451350
52005-07-26 00:00:00472150
62005-02-20 00:00:00561100
72005-07-30 00:00:00261120
82005-09-12 00:00:0021615500
92005-09-30 00:00:005151230
102005-10-27 00:00:005151230
112005-11-28 00:00:005151250
122005-12-22 00:00:005151250
132005-08-11 00:00:0031312200
142005-10-23 00:00:00214166000
152005-02-03 00:00:001958
162005-03-11 00:00:001957
172005-03-18 00:00:002938
182005-04-20 00:00:001988
192005-05-13 00:00:001957
202005-06-11 00:00:00293150
212006-01-12 00:00:003101100
222006-03-12 00:00:0015310
232005-06-05 00:00:00181300
242005-06-20 00:00:00368150

Data in FamilyMembers table:

member_idstatusmember_namebirthday
1fatherHeadley Quincey1960-05-13 00:00:00
2motherFlavia Quincey1963-02-16 00:00:00
3sonAndie Quincey1983-06-05 00:00:00
4daughterLela Quincey1985-06-07 00:00:00
5daughterAnnie Quincey1988-04-10 00:00:00
6fatherErnest Forrest1961-09-11 00:00:00
7motherConstance Forrest1968-09-06 00:00:00

To solve this problem, run a query that combines record fields from first table with the fields of another, if the condition is met that the product buyer (family_member) matches the family member ID (member_id):

MySQL
SELECT *
FROM Payments 
    JOIN FamilyMembers ON family_member = member_id;

In the result you can see that every record of Payments table appended with a data about family member who made the purchase. Note thefamily_member and member_id fields — they are the same, which was reflected in the request. запросе.

payment_iddatefamily_membergoodamountunit_pricemember_idstatusmember_namebirthday
12005-02-12 00:00:0011120001fatherHeadley Quincey1960-05-13 00:00:00
22005-03-23 00:00:0021121002motherFlavia Quincey1963-02-16 00:00:00
32005-05-14 00:00:00345203sonAndie Quincey1983-06-05 00:00:00
42005-07-22 00:00:004513504daughterLela Quincey1985-06-07 00:00:00
52005-07-26 00:00:004721504daughterLela Quincey1985-06-07 00:00:00
62005-02-20 00:00:005611005daughterAnnie Quincey1988-04-10 00:00:00
72005-07-30 00:00:002611202motherFlavia Quincey1963-02-16 00:00:00
82005-09-12 00:00:00216155002motherFlavia Quincey1963-02-16 00:00:00
92005-09-30 00:00:0051512305daughterAnnie Quincey1988-04-10 00:00:00
102005-10-27 00:00:0051512305daughterAnnie Quincey1988-04-10 00:00:00
112005-11-28 00:00:0051512505daughterAnnie Quincey1988-04-10 00:00:00
122005-12-22 00:00:0051512505daughterAnnie Quincey1988-04-10 00:00:00
132005-08-11 00:00:00313122003sonAndie Quincey1983-06-05 00:00:00
142005-10-23 00:00:002141660002motherFlavia Quincey1963-02-16 00:00:00
152005-02-03 00:00:0019581fatherHeadley Quincey1960-05-13 00:00:00
162005-03-11 00:00:0019571fatherHeadley Quincey1960-05-13 00:00:00
172005-03-18 00:00:0029382motherFlavia Quincey1963-02-16 00:00:00
182005-04-20 00:00:0019881fatherHeadley Quincey1960-05-13 00:00:00
192005-05-13 00:00:0019571fatherHeadley Quincey1960-05-13 00:00:00
202005-06-11 00:00:002931502motherFlavia Quincey1963-02-16 00:00:00
212006-01-12 00:00:0031011003sonAndie Quincey1983-06-05 00:00:00
222006-03-12 00:00:00153101fatherHeadley Quincey1960-05-13 00:00:00
232005-06-05 00:00:001813001fatherHeadley Quincey1960-05-13 00:00:00
242005-06-20 00:00:003681503sonAndie Quincey1983-06-05 00:00:00

Using WHERE to join tables

You can also use WHERE operator for joining tables internally. For example, the above query that written using INNER JOIN will look like this: так:

MySQL
SELECT *
FROM Payments, FamilyMembers
WHERE family_member = member_id;

OUTER JOIN

There are three types of external connections: left, right, and FULL. By default, it is complete.

The main difference between an external connection and an internal one is that it necessarily returns all rows in one (LEFT, RIGHT) or two (FULL) tables.

External left join (LEFT OUTER JOIN)

A join that returns all values from the left table, connected with the corresponding values from the right table if they satisfy the connection condition, or replaces them with NULL in the opposite case.

For example we will get a call schedule from the database combined with the corresponding classes in the class schedule:

MySQL
SELECT *
FROM Timepair
    LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id;

Data in the Timepair table (call schedule):

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 (class schedule):

iddateclassnumber_pairteachersubjectclassroom
12019-09-019111147
42019-09-02914313
72019-09-03915636
102019-09-04919939
132019-09-059131343
162019-08-30912434
22019-09-01928213
52019-09-02922434
82019-09-039213737
112019-09-0492101040
142019-09-059211147
172019-08-30928213
32019-09-01934313
62019-09-02936535
92019-09-03936838
122019-09-049331141
152019-09-05935636
182019-08-30936535
192019-08-309410147

The selection includes all rows from the left table, supplemented with data about classes. It is noteworthy that at the end of the table there are rows with fields filled in with NULL. These are the rows for which there were no corresponding classes, but they are present in the left table, so they were also output.

timepair.idstart_pairend_pairschedule.iddateclassnumber_pairteachersubjectclassroom
108:30:0009:15:0042019-09-02914313
108:30:0009:15:0072019-09-03915636
108:30:0009:15:00162019-08-30912434
108:30:0009:15:00102019-09-04919939
108:30:0009:15:0012019-09-019111147
108:30:0009:15:00132019-09-059131343
209:20:0010:05:0022019-09-01928213
209:20:0010:05:0052019-09-02922434
209:20:0010:05:00172019-08-30928213
209:20:0010:05:0082019-09-039213737
209:20:0010:05:00112019-09-0492101040
209:20:0010:05:00142019-09-059211147
310:15:0011:00:00122019-09-049331141
310:15:0011:00:00182019-08-30936535
310:15:0011:00:00152019-09-05935636
310:15:0011:00:0092019-09-03936838
310:15:0011:00:0062019-09-02936535
310:15:0011:00:0032019-09-01934313
411:05:0011:50:00192019-08-309410147
512:50:0013:35:00NULLNULLNULLNULLNULLNULLNULL
613:40:0014:25:00NULLNULLNULLNULLNULLNULLNULL
714:35:0015:20:00NULLNULLNULLNULLNULLNULLNULL
815:25:0016:10:00NULLNULLNULLNULLNULLNULLNULL

External right join (RIGHT OUTER JOIN)

A join that returns all values from the right table connected to the corresponding values from the left table if they satisfy the join condition, or replaces them with NULL if they do not.

External full join (FULL OUTER JOIN)

A connection that performs an internal connection of records and complements them with a left external connection and a right external connection.

The algorithm of full join:

  1. A table is formed based on an internal connection (INNER JOIN).
  2. Values that were not included in the formation result from the left table are added to the table (LEFT OUTER JOIN).
  3. Values that were not included in the formation result from the right table are added to the table (RIGHT OUTER JOIN).
The FULL JOIN connection is not implemented in all DBMSs. For example, MySQL doesn't have it, but it can be emulated very easily:
MySQL
SELECT * 
FROM left_table 
LEFT JOIN right_table 
    ON right_table.field = left_table.field

UNION ALL

SELECT * 
FROM left_table 
RIGHT JOIN right_table 
    ON right_table.field = left_table.field
WHERE left_table.key IS NULL

Basic queries for different ways to join tables

SchemeJOIN query
Retrieves all data from the left table connected to the corresponding data from the right:
MySQL
SELECT table_fields
FROM left_table LEFT JOIN right_table
    ON right_table.field = left_table.field
Retrieves all data from the right table connected to the corresponding data from the left:
MySQL
SELECT table_fields
FROM left_table RIGHT JOIN right_table
    ON right_table.field = left_table.field
Getting data related only to the left table:
MySQL
SELECT table_fields
FROM left_table RIGHT JOIN right_table
    ON right_table.field = left_table.field
WHERE right_table.field IS NULL
Getting data related only to the right table:
MySQL
SELECT table_fields
FROM left_table RIGHT JOIN right_table
    ON right_table.field = left_table.field
WHERE left_table.field IS NULL
Getting data related to both the left and right tables:
MySQL
SELECT table_fields
FROM left_table INNER JOIN right_table
    ON right_table.field = left_table.field
Getting all data related to the left and right tables, as well as their internal connection:
MySQL
SELECT table_fields 
FROM left_table
    FULL OUTER JOIN right_table
    ON right_table.field = left_table.field
Getting data that is not related to the left and right tables at the same time (reverse INNER JOIN):
MySQL
SELECT table_fields 
FROM left_table
    FULL OUTER JOIN right_table
    ON right_table.field = left_table.field
WHERE left_table.field IS NULL
    OR right_table.field IS NULL

Tasks for self-testing

Sampling constraint, LIMIT operator
Sampling constraint, LIMIT operator