The labs on this site need JavaScript to run. The same content is written out below.

Fundamentals › Database

Joins

What does a join attach, and what does ON decide?

A join is a SELECT that attaches the rows of two tables side by side. The orders table in this lab holds only the number of the member who placed the order (member_id), and the name is in the member table. So to see the item that was ordered together with the name of the person who ordered it, you have to connect the two tables. JOIN member m decides which table to attach, and ON o.member_id = m.id decides "which rows are a pair." For each order row, the database finds the member row that matches the ON condition and attaches it on the same line. In this lab you remove JOIN, remove the ON condition, or add an order that has no matching member, and run the same SQL each time. You see how many lines the result has and what shape it takes.

Checked against real execution · Ran all 6 combinations on H2 2.4.240 and checked against the results, and checked the rules against the MySQL documentation

Concepts in this lab

JOIN (INNER, OUTER)

The operation that combines rows of two tables according to a condition. INNER JOIN includes only the rows where the condition matches on both sides. LEFT OUTER JOIN includes every row of the left table and fills in NULL when there is no match on the right. Choose between them based on whether data without a match must stay in the result.

ON clause

This is the condition that decides which rows are a pair when you connect two tables with JOIN. When you write ON and a conditional expression after JOIN, as in JOIN member m ON o.member_id = m.id, the database treats two rows for which this condition is true as a pair and attaches them on one line. The conditional expression has the same form as one you can write in WHERE. Usually ON is used to decide how to connect the tables, and WHERE is used to pick the rows to put in the result. In ON you usually write a condition that compares columns of the two tables that the JOIN connects. In MySQL, a JOIN without ON works like a CROSS JOIN, and it attaches every row to every row without sorting out pairs.

Table alias

This is a short second name that you give to a table in the FROM clause. You write it after the table name, as in FROM orders o, or with AS, as in FROM orders AS o, and the two ways mean the same. With an alias, you can say which table a column belongs to in a short way in SELECT or ON, as in o.item and m.name. When two tables have a column with the same name (for example, id), you often have to say which one you mean with the alias. A column alias (AS) is a name for a column, and a table alias is a name for a table.

Cartesian product

The result of producing every combination of two sets. In SQL, if you join two collections that belong to one entity at the same time, the number of result rows grows to the product of the sizes of the two collections. Unnecessarily large amounts of data are transferred and take up memory, so avoid loading several collections in a single join.

Try these

• Default settings — For each order row, the member row that matches the ON condition is attached, and the item and the name of the member come out on one line.
• Removing JOIN and reading only orders — Only the member_id numbers are read, not the names.
• Joining without an ON condition — Pairs are not sorted out, and every member is attached to every order.
• An order has no matching member — An order with no member does not appear in the result of the JOIN.

Running with the default settings

1. CREATE TABLE and INSERT: The database creates the two tables member and orders and adds rows. member has 2 members (id 1 Alice, id 2 Bob), and orders has 3 orders (#10 Keyboard, #11 Mouse, #12 Monitor). orders says who placed each order only by the number in the member_id column (the member_id of order 10 is 1). The name for that number is only in the member table.
2. SELECT o.item, m.name FROM orders o JOIN member m ON o.member_id = m.id: This SELECT reads the item that was ordered together with the name of the member. FROM orders o means read the orders table (o is a short name for orders), JOIN member m means attach the member table (m is a short name for member), and ON o.member_id = m.id means that an order row and a member row are a pair when the member_id of the order equals the id of the member. If we write out in order how the result is made, the database finds, for each row of orders, the member row that matches the ON condition and attaches it next to it. Which table the database actually starts from and how it finds the rows is up to the database.
3. The database first looks at the order 10 row (member_id 1, Keyboard) in orders. It looks for the member row that matches the ON condition o.member_id = m.id. The member_id of this order is 1, so the row with id 1, the Alice row, is its pair.
4. The database attaches the order 10 row and the Alice row side by side. From the attached row it takes out o.item (Keyboard) and m.name (Alice) that the SELECT names, and puts the one line "Keyboard, Alice" into the result. A name that orders did not have has appeared on the same line. This is a join.
5. The database finds the row with id 1, Alice, as the pair of the order 11 row (member_id 1, Mouse), attaches it, and puts the one line "Mouse, Alice" into the result. The same Alice row was attached to an earlier order too. One member row can be attached to many orders.
6. The database finds the row with id 2, Bob, as the pair of the order 12 row (member_id 2, Monitor), attaches it, and puts the one line "Monitor, Bob" into the result.
7. Result of the SELECT: The database looked at every row of orders. Rows in the result: 3. One line was made for each of the 3 orders.

Result of this run

• A join attaches the rows of two tables side by side. JOIN decides which table to attach and ON decides which rows are a pair, and for each order row the database found the member row that matches the ON condition and attached it on one line. So the item that was ordered and the name of the member came out on one line.
• 3 orders became 3 rows in the result. The Alice row was attached to two orders, because a row that matches the ON condition can be attached to many orders.

Code

CREATE TABLE member (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE orders (id INT PRIMARY KEY, member_id INT, item VARCHAR(20));
INSERT INTO member VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO orders VALUES (10, 1, 'Keyboard'), (11, 1, 'Mouse'),
    (12, 2, 'Monitor');

-- Join: attach the rows of member next to the rows of orders
SELECT o.item, m.name
FROM orders o
-- ON: rows are a pair when member_id equals the id of the member
JOIN member m ON o.member_id = m.id
ORDER BY o.id;

Assumptions of this model

The model was checked against real execution on H2 2.4.240 (an in-memory database, through JDBC). For all 6 combinations, the whole SQL on the screen (including the comment lines) was run character by character, one statement at a time, and the number of rows that the SELECT returned and the values of all the rows (item and name, or member_id) were checked against the model. If the result had more than four rows, it was shortened to the first four rows and the total number of rows, and the same rule was used when checking. Besides this, the values that the step sentences rely on were also checked separately on H2: the number of rows in orders, that no member with id 9 exists in member, the id of the member that is the pair, and that the number of rows of a CROSS JOIN without ON is the product of the numbers of rows of the two tables. The definitions and rules were checked by opening the MySQL 8.4 documentation. In section 15.2.13.2 JOIN Clause, MySQL treats JOIN, CROSS JOIN, and INNER JOIN as the same thing that can be used in place of each other (standard SQL calls it INNER JOIN if there is ON and CROSS JOIN if there is not), a Cartesian product of the two tables is made when there is no condition, and the ON condition is a conditional expression that can be used in WHERE and is usually used to decide how to connect the tables. Table aliases follow the same section and the FROM description in section 15.2.13 SELECT Statement (tbl_name AS alias_name or tbl_name alias_name). However, the two documents do not separately say that a row without a pair does not appear in the result of an INNER JOIN, so that behavior was checked by execution on H2. I opened the documentation with a tool that summarizes a web page, and I did not check the original text character by character. The check by execution on MySQL was not done. The step sentences write out in order how the result is made. Which table the database actually reads first and how it finds the pairs is decided by the database, and the order of the lines in the result is decided by the ORDER BY on the screen. LEFT JOIN, which also keeps rows without a pair, is not covered, and it is covered in the lab "JOIN and GROUP BY" in the study section.

Previous lab: WHERE · Next lab: GROUP BY and aggregation