Understanding MySQL JOIN Types with Visual Diagrams and Code Examples

Table joins are a frequent operation in relational databases, and MySQL supports several JOIN types such as INNER, LEFT, RIGHT, and FULL OUTER. Memorizing each type can be confusing, so this guide uses visual diagrams and concrete examples to clarify their semantics.

1. Preparation

Two tables are created: t_order (order_id, order_no, customer_id) and t_customer (id, customer_name). Sample rows are inserted into both tables.

CREATE TABLE `t_order`(
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_no` int(11) DEFAULT NULL COMMENT '订单号',
  `customer_id` int(11) DEFAULT NULL COMMENT '客户id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert data
INSERT INTO `t_order` VALUES ('1','1001','1');
INSERT INTO `t_order` VALUES ('2','1002','26');

CREATE TABLE `t_customer`(
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `customer_name` varchar(255) DEFAULT NULL COMMENT '客户姓名 ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert data
INSERT INTO `t_customer` VALUES ('1','John');
INSERT INTO `t_customer` VALUES ('2','Tom');

2. INNER JOIN

Returns only rows where the join condition matches in both tables (the intersection).

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A INNER JOIN t_customer B
ON A.customer_id = B.id;

The result contains a single row because only one order has a matching customer.

3. LEFT JOIN

Returns all rows from the left table and matching rows from the right table; non‑matching right‑side columns are NULL.

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A LEFT JOIN t_customer B
ON A.customer_id = B.id;

The row with customer_id = 26 shows NULL for the customer fields because there is no matching customer.

4. RIGHT JOIN

Returns all rows from the right table and matching rows from the left table; non‑matching left‑side columns are NULL.

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A RIGHT JOIN t_customer B
ON A.customer_id = B.id;

The customer with id = 2 has NULL values for order columns because no order references that customer.

5. FULL OUTER JOIN (simulated with UNION)

MySQL 5.7 does not support FULL OUTER JOIN directly, so a UNION of LEFT and RIGHT joins is used to obtain the union of both tables.

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A LEFT JOIN t_customer B ON A.customer_id = B.id
UNION
SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A RIGHT JOIN t_customer B ON A.customer_id = B.id;

6. LEFT JOIN EXCLUDING INNER JOIN

Find rows present in the left table but not in the right table.

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A LEFT JOIN t_customer B ON A.customer_id = B.id
WHERE B.id IS NULL;

7. RIGHT JOIN EXCLUDING INNER JOIN

Find rows present in the right table but not in the left table.

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A RIGHT JOIN t_customer B ON A.customer_id = B.id
WHERE A.id IS NULL;

8. OUTER JOIN EXCLUDING INNER JOIN

Find rows that have no matching counterpart in either direction.

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A LEFT JOIN t_customer B ON A.customer_id = B.id
WHERE B.id IS NULL
UNION
SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A RIGHT JOIN t_customer B ON A.customer_id = B.id
WHERE A.id IS NULL;

9. Summary

The article also notes that CROSS JOIN produces the Cartesian product of two tables, which is not covered in detail here. A final composite diagram summarizes all join types for quick reference.