MySQL Joins Visual Guide: Inner, Left, Right, Full Outer with SQL

Introduction

Table joins are a fundamental database operation. MySQL supports several join types—inner join, left outer join, right outer join, and full outer join—each with distinct semantics. This article uses a visual, example-driven approach to make the differences memorable.

Sample Data Preparation

Two tables are created: t_order (orders) and t_customer (customers). The customer_id in t_order references the primary key of t_customer.

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');

The order table contains two rows: order 1001 linked to customer 1, and order 1002 linked to customer 26 (which does not exist in the customer table). The customer table contains customers 1 (John) and 2 (Tom).

INNER JOIN

Returns only rows that satisfy the join condition in both tables—effectively 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;

Result: only the row where customer_id = 1 matches, producing a single combined row.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table ( t_order), with matching rows from the right table ( t_customer). Non-matching right columns become 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;

Result: both order rows appear. For customer_id 26, the customer columns are NULL.

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table ( t_customer), with matching rows from the left table. Non-matching left columns become 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;

Result: both customer rows appear. For customer 2 (Tom), the order columns are NULL because no order references customer 2.

FULL OUTER JOIN

MySQL 5.7 does not support FULL OUTER JOIN directly. It can be simulated by combining a LEFT JOIN and a RIGHT JOIN with UNION (which removes duplicates). This yields 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;

Result: all rows from both tables, with NULLs where no match exists.

LEFT JOIN EXCLUDING INNER JOIN

Returns rows present in the left table but not in the right table (left-only). Achieved by adding WHERE B.id IS NULL to a LEFT JOIN.

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;

Result: only the order with customer_id 26 (no matching customer).

RIGHT JOIN EXCLUDING INNER JOIN

Returns rows present in the right table but not in the left table (right-only). Uses WHERE A.id IS NULL on a RIGHT JOIN.

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;

Result: only customer 2 (Tom) who has no orders.

OUTER JOIN EXCLUDING INNER JOIN (Symmetric Difference)

Returns rows from both tables that have no match in the other table. Implemented as a UNION of the two exclusion queries above.

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;

Result: the order with customer_id 26 and customer 2 (Tom).

Summary

The article concludes with a consolidated diagram summarizing all join types. It also notes that CROSS JOIN (Cartesian product) is not covered in detail.