Multi-table queries, the JOIN operator
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).
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;
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.key IS NULL
Basic queries for different ways to join tables
|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):|
Tasks for self-testing
Sampling constraint, LIMIT operator
Sampling constraint, LIMIT operator