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

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:

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.

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:

Data in FamilyMembers table:

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

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.

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:

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:

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

Data in the Timepair table (call schedule):

Data in the Schedule table (class schedule):

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.

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

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.field IS NULL

Basic queries for different ways to join tables

Scheme	JOIN 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 LEFT 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