Master MySQL Joins: From Cartesian Products to Inner, Left & Right Joins

Overview

MySQL’s most powerful feature is its ability to join tables during data retrieval. Single‑table queries often cannot satisfy complex requirements, so we need to join one or more tables and filter the result set.

Data Preparation

We reuse the tables from the previous lesson: a classes table and a students table.

<code>mysql> select * from classes;
+----------+-----------+
| classid  | classname |
+----------+-----------+
| 1        | 初三一班   |
| 2        | 初三二班   |
| 3        | 初三三班   |
| 4        | 初三四班   |
+----------+-----------+
4 rows in set</code>

<code>mysql> select * from students;
+-----------+-------------+-------+----------+
| studentid | studentname | score | classid  |
+-----------+-------------+-------+----------+
| 1         | brand       | 97.5  | 1        |
| 2         | helen       | 96.5  | 1        |
| 3         | lyn         | 96    | 1        |
| 4         | sol         | 97    | 1        |
| 7         | b1          | 81    | 2        |
| 8         | b2          | 82    | 2        |
| 13        | c1          | 71    | 3        |
| 14        | c2          | 72.5  | 3        |
| 19        | lala        | 51    | 0        |
+-----------+-------------+-------+----------+
9 rows in set</code>

Cartesian Product (Cross Join)

The Cartesian product combines every row of one set with every row of another. If set A has m rows and set B has n rows, the result contains m × n rows.

In SQL the Cartesian product is produced by a CROSS JOIN or by listing tables separated by commas without a join condition.

<code>select cname1, cname2, ...
from tname1, tname2;
-- or
select cname1, cname2, ...
from tname1 cross join tname2;</code>

For the two tables above the Cartesian product yields 4 × 9 = 36 rows.

Inner Join (INNER JOIN)

An inner join returns only the rows where the join condition matches in both tables – essentially the intersection of the two sets.

<code>select cname
from tname1 inner join tname2 on join_condition;
-- or
select cname
from tname1 join tname2 on join_condition;
-- or
select cname
from tname1, tname2 where join_condition;</code>

Example:

<code>mysql> select a.classname, b.studentname, b.score
from classes a inner join students b on a.classid = b.classid;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班   | brand       | 97.5  |
| 初三一班   | helen       | 96.5  |
| 初三一班   | lyn         | 96    |
| 初三一班   | sol         | 97    |
| 初三二班   | b1          | 81    |
| 初三二班   | b2          | 82    |
| 初三三班   | c1          | 71    |
| 初三三班   | c2          | 72.5  |
+-----------+-------------+-------+
8 rows in set</code>

Only classes that have matching students appear; rows without a match are omitted.

Left Join (LEFT JOIN)

A left join (left outer join) returns all rows from the left table and the matching rows from the right table. Non‑matching right‑table columns are returned as NULL .

<code>select cname
from tname1 left join tname2 on join_condition;</code>

Example:

<code>mysql> select a.classname, b.studentname, b.score
from classes a left join students b on a.classid = b.classid;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班   | brand       | 97.5  |
| 初三一班   | helen       | 96.5  |
| 初三一班   | lyn         | 96    |
| 初三一班   | sol         | 97    |
| 初三二班   | b1          | 81    |
| 初三二班   | b2          | 82    |
| 初三三班   | c1          | 71    |
| 初三三班   | c2          | 72.5  |
| 初三四班   | NULL        | NULL  |
+-----------+-------------+-------+
9 rows in set</code>

Right Join (RIGHT JOIN)

A right join (right outer join) returns all rows from the right table and the matching rows from the left table; non‑matching left‑table columns become NULL .

<code>select cname
from tname1 right join tname2 on join_condition;</code>

Example:

<code>mysql> select a.classname, b.studentname, b.score
from classes a right join students b on a.classid = b.classid;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| 初三一班   | brand       | 97.5  |
| 初三一班   | helen       | 96.5  |
| 初三一班   | lyn         | 96    |
| 初三一班   | sol         | 97    |
| 初三二班   | b1          | 81    |
| 初三二班   | b2          | 82    |
| 初三三班   | c1          | 71    |
| 初三三班   | c2          | 72.5  |
| NULL      | lala        | 51    |
+-----------+-------------+-------+
9 rows in set</code>

Join with Aggregation Functions

Joins are often combined with aggregate functions to summarise data per group.

<code>select a.classname as '班级名称',
       count(b.studentid) as '总人数',
       sum(b.score) as '总分',
       avg(b.score) as '平均分'
from classes a inner join students b on a.classid = b.classid
group by a.classid, a.classname;</code>

Result:

<code>+-----------+--------+------+----------+
| 班级名称  | 总人数 | 总分 | 平均分   |
+-----------+--------+------+----------+
| 初三一班   | 4      | 387  | 96.75    |
| 初三二班   | 2      | 163  | 81.5     |
| 初三三班   | 2      | 143.5| 71.75    |
+-----------+--------+------+----------+
3 rows in set</code>

Join with Additional Filtering

After a join you can further restrict the result set with a WHERE clause.

<code>select a.classname, b.studentname, b.score
from classes a inner join students b on a.classid = b.classid
where a.classid = 1;</code>

This returns only the students belonging to class 1.

Summary

Always include a join condition; otherwise you get a Cartesian product, which usually produces meaningless data.

SQL style guides recommend using the explicit INNER JOIN syntax.

The performance difference among INNER , LEFT , and RIGHT joins is negligible; the real impact comes from data volume, join conditions, and index usage.

Choose the join type based on business needs: INNER JOIN for rows that exist in both tables, LEFT JOIN to keep all rows from the left table, and RIGHT JOIN to keep all rows from the right table.

More joins increase resource consumption; remove unnecessary joins and columns to improve query performance.