Master MySQL Joins: Visual Guide to INNER, LEFT, RIGHT, and FULL OUTER

Preparation

Two tables are created: t_order (order information) and t_customer (customer information). The customer_id column in t_order references the primary key id in t_customer. Sample data is 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 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 INTO `t_customer` VALUES ('1','John');
INSERT INTO `t_customer` VALUES ('2','Tom');

1. INNER JOIN

Returns rows that satisfy the join condition 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 shows only the matching row where customer_id exists in both tables.

2. 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;

Rows with customer_id = 26 have NULL for the customer fields because no matching customer exists.

3. 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 result includes the customer with id = 2 even though there is no corresponding order, showing NULL for order fields.

4. FULL OUTER JOIN (Simulated)

MySQL 5.7 does not support true FULL OUTER JOIN. It can be simulated by UNION of LEFT and RIGHT joins.

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;

5. 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;

6. 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;

7. OUTER JOIN EXCLUDING INNER JOIN

Find rows that have no matching counterpart in either table (the symmetric difference).

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;

Conclusion

CROSS JOIN produces the Cartesian product of two tables and is not covered here. A final summary diagram consolidates all join types for quick reference.