Log in
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.
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:
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:
- A table is formed based on an internal connection (INNER JOIN).
- Values that were not included in the formation result from the left table are added to the table (LEFT OUTER JOIN).
- 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:
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:
| |
Retrieves all data from the right table connected to the corresponding data from the left:
| |
Getting data related only to the left table:
| |
Getting data related only to the right table:
| |
Getting data related to both the left and right tables:
| |
Getting all data related to the left and right tables, as well as their internal connection:
| |
Getting data that is not related to the left and right tables at the same time (reverse INNER JOIN):
|