Visually explaining MySQL JOINs

In 2007 Jeff Attwood published a blog post A Visual Explanation of SQL Joins. This is a short revised version according to MySQL 5.7 JOIN.

We will be using the same data sets as in the original article. See below for the SQL data to perform the statements on your machine.

1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

INNER JOIN

The INNER JOIN shows only those that are in TableA and TableB.

SELECT *
FROM TableA INNER JOIN TableB
USING (name);

+--------+----+----+
| name   | id | id |
+--------+----+----+
| Pirate |  1 |  2 |
| Ninja  |  3 |  4 |
+--------+----+----+

LEFT JOIN

The LEFT JOIN shows only those that are in TableA and where there exists a reference in TableB.

SELECT *
FROM TableA LEFT JOIN TableB
USING (name);

+-----------+----+------+
| name      | id | id   |
+-----------+----+------+
| Pirate    |  1 |    2 |
| Monkey    |  2 | NULL |
| Ninja     |  3 |    4 |
| Spaghetti |  4 | NULL |
+-----------+----+------+

SQL Code

CREATE TABLE `TableA` (
  `id` int(11) NOT NULL,
  `name` varchar(23) DEFAULT NULL
);
INSERT INTO `TableA` (`id`, `name`)
VALUES ('1', 'Pirate'), ('2', 'Monkey'), ('3', 'Ninja'), ('4', 'Spaghetti');
CREATE TABLE `TableB` (
  `id` int(11) NOT NULL,
  `name` varchar(23) DEFAULT NULL
);
INSERT INTO `TableB` (`id`, `name`)
VALUES ('1', 'Rutabaga'), ('2', 'Pirate'), ('3', 'Darth Vader'), ('4', 'Ninja');