MySQL JOIN

In MySQL, JOIN statements are used to combine two or more tables in the database.

For example, there are user and user hobby tables. The two tables are related by the user_id column. If you want to query a user’s profile, you need to join the two tables to query user basic info and hobbies.

MySQL JOIN type

MySQL has supported the following 4 types of joins:

• Inner join (INNER JOIN)
• Left join (LEFT JOIN)
• Right join (RIGHT JOIN)
• Cross join (CROSS JOIN)

MySQL does not support full joins (FULL OUTER JOIN) now.

JOIN examples

Let us understand the 4 joins types by some examples. In the follow examples, we will use two tables: student and student_score.

Create tables as demonstration

First, use the following SQL statements to create student and student_score tables:

CREATE TABLE `student` (
  `student_id` int NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`student_id`)
);

CREATE TABLE `student_score` (
  `student_id` int NOT NULL,
  `subject` varchar(45) NOT NULL,
  `score` int NOT NULL
);

Then, insert some testing rows into the two tables:

INSERT INTO `student` (`student_id`, `name`)
VALUES (1, 'Tim'), (2, 'Jim'), (3, 'Lucy');

INSERT INTO `student_score` (`student_id`, `subject`, `score`)
VALUES (1, 'English', 90), (1, 'Math', 80),
(2, 'English', 85), (2, 'Math', 88), (5, 'English', 92);

Then, let’s take a look at the rows int the two tables using select statement:

SELECT * FROM student;

+------------+------+
| student_id | name |
+------------+------+
|          1 | Tim  |
|          2 | Jim  |
|          3 | Lucy |
+------------+------+
3 rows in set (0.01 sec)

SELECT * FROM student_score;

+------------+---------+-------+
| student_id | subject | score |
+------------+---------+-------+
|          1 | English |    90 |
|          1 | Math    |    80 |
|          2 | English |    85 |
|          2 | Math    |    88 |
|          5 | English |    92 |
+------------+---------+-------+
5 rows in set (0.00 sec)

Note: the last row whose student_id column has a value 5 in the student_score table, and there is no rows whose student_id column value is 5 in the student table.

Cross Join

Cross join returns the Cartesian product of rows of two tables, that is all possible combinations of all rows in the two tables. It is equivalent to the inner join has no join condition or the join condition is always true.

If a table has m rows and another table has n rows, the cross join of the two tables returns m * n rows.

Explicit cross join of student and student_score tables:

SELECT
  student.*,
  student_score.*
FROM
  student CROSS JOIN student_score;

Implicit cross join of student and student_score tables:

SELECT
  student.*,
  student_score.*
FROM
  student, student_score;

The outputs of the two statements are the same.

+------------+------+------------+---------+-------+
| student_id | name | student_id | subject | score |
+------------+------+------------+---------+-------+
|          3 | Lucy |          1 | English |    90 |
|          2 | Jim  |          1 | English |    90 |
|          1 | Tim  |          1 | English |    90 |
|          3 | Lucy |          1 | Math    |    80 |
|          2 | Jim  |          1 | Math    |    80 |
|          1 | Tim  |          1 | Math    |    80 |
|          3 | Lucy |          2 | English |    85 |
|          2 | Jim  |          2 | English |    85 |
|          1 | Tim  |          2 | English |    85 |
|          3 | Lucy |          2 | Math    |    88 |
|          2 | Jim  |          2 | Math    |    88 |
|          1 | Tim  |          2 | Math    |    88 |
|          3 | Lucy |          5 | English |    92 |
|          2 | Jim  |          5 | English |    92 |
|          1 | Tim  |          5 | English |    92 |
+------------+------+------------+---------+-------+
15 rows in set (0.00 sec)

Inner join

The inner join combines the rows FROM two tables based on join conditions. Inner join is equivalent to cross join with filtering conditions.

The inner join evaluates each row of the first table with each row of the second table, and if the given join conditions are met, the rows of the two tables are combined together as a row in the result set.

The following SQL statement uses inner join to join student and student_score tables:

SELECT
  student.*,
  student_score.*
FROM
  student
  INNER JOIN student_score
  ON student.student_id = student_score.student_id;

Equivalent to:

SELECT
  student.*,
  student_score.*
FROM
  student, student_score
  WHERE student.student_id = student_score.student_id;

+------------+------+------------+---------+-------+
| student_id | name | student_id | subject | score |
+------------+------+------------+---------+-------+
|          1 | Tim  |          1 | English |    90 |
|          1 | Tim  |          1 | Math    |    80 |
|          2 | Jim  |          2 | English |    85 |
|          2 | Jim  |          2 | Math    |    88 |
+------------+------+------------+---------+-------+
4 rows in set (0.00 sec)

Note that in the output result set, the student_id column does not include 3 and 5.

As the inner join uses the same column names for equivalent matching, so you can use the USING clause instead, as following:

SELECT
  student.*,
  student_score.*
FROM
  student
  INNER JOIN student_score USING(student_id);

Left join

Left join is the abbreviation of left outer join, and left join requires join conditions.

When a table left join another table, the first table is called left table, and the second table is called right table. For example A LEFT JOIN B, A is the left table and B is the right table.

The left join selects all rows from the left table, and matches each row of the right table according to the join conditions. If the row of the left table has a matched row in the right table, the two rows are combined into a new row and returned; if not, the row of the left table and NULL values ​​are combined into a new row and returned.

The following SQL statement uses left join to join student and student_score tables:

SELECT
  student.*,
  student_score.*
FROM
  student
  LEFT JOIN student_score
  ON student.student_id = student_score.student_id;

+------------+------+------------+---------+-------+
| student_id | name | student_id | subject | score |
+------------+------+------------+---------+-------+
|          1 | Tim  |          1 | Math    |    80 |
|          1 | Tim  |          1 | English |    90 |
|          2 | Jim  |          2 | Math    |    88 |
|          2 | Jim  |          2 | English |    85 |
|          3 | Lucy |       NULL | NULL    |  NULL |
+------------+------+------------+---------+-------+
5 rows in set (0.00 sec)

Notice:

1.s The result set contains all rows of the student table. 2. There are no rows which student_id column value is 3 in the student_score table, so in the last row, the columns from student_score table values are NULL. 3. There are multiple rows which student_id column values are 1 and 2.

You can use the USING clause instead:

SELECT
  student.*,
  student_score.*
FROM
  student
  LEFT JOIN student_score USING(student_id);

Right join

Right join is the abbreviation of right outer join, and right join requires join conditions.

The processing logic of the right join is opposite to the left join. The right join is based on the rows from the right table.

The following SQL statement uses right join to join student and student_score tables:

SELECT
  student.*,
  student_score.*
FROM
  student
  RIGHT JOIN student_score
  ON student.student_id = student_score.student_id;

+------------+------+------------+---------+-------+
| student_id | name | student_id | subject | score |
+------------+------+------------+---------+-------+
|          1 | Tim  |          1 | English |    90 |
|          1 | Tim  |          1 | Math    |    80 |
|          2 | Jim  |          2 | English |    85 |
|          2 | Jim  |          2 | Math    |    88 |
|       NULL | NULL |          5 | English |    92 |
+------------+------+------------+---------+-------+
5 rows in set (0.00 sec)

In fact, A RIGHT JOIN B is B LEFT JOIN A, the right join and therefore rarely used.

You can use the following left join statement instead:

SELECT
  student.*,
  student_score.*
FROM
  student_score
  LEFT JOIN student
  ON student.student_id = student_score.student_id;

+------------+------+------------+---------+-------+
| student_id | name | student_id | subject | score |
+------------+------+------------+---------+-------+
|          1 | Tim  |          1 | English |    90 |
|          1 | Tim  |          1 | Math    |    80 |
|          2 | Jim  |          2 | English |    85 |
|          2 | Jim  |          2 | Math    |    88 |
|       NULL | NULL |          5 | English |    92 |
+------------+------+------------+---------+-------+
5 rows in set (0.00 sec)

Conclusion

This article described the joins statement in MySQL, including cross join, inner join, left join and right join. The key points of the join include:

• The join is used to combine the rows of the two tables.
• The cross join returns all possible combinations of all rows in the two tables.
• The inner join combines the rows in the two tables based on the join condition.
• The left join combines the rows in the two tables based on the left table.
• The right join combines the rows in the two tables based on the right table.