Understanding MySQL Table Joins: INNER, LEFT, RIGHT, FULL OUTER and Advanced Variants

Welcome to the "Full‑Stack Architecture" series. In this tutorial we focus on MySQL table joins, a fundamental operation for combining data from multiple tables. We first create two sample tables— t_order and t_customer —and insert a few rows to illustrate various join scenarios.

CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `order_no` int(11) DEFAULT NULL COMMENT 'Order Number',
  `customer_id` int(11) DEFAULT NULL COMMENT 'Customer 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 'Primary Key',
  `customer_name` varchar(255) DEFAULT NULL COMMENT 'Customer Name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

The data in the two tables look like the diagrams below:

1. INNER JOIN

INNER JOIN returns only rows where the join condition matches in both tables (the intersection). Example:

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 set contains a single row because only the order with customer_id = 1 matches a customer record.

2. LEFT JOIN

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 result shows the order with customer_id = 26 having NULL values for the customer columns because no matching customer exists.

3. RIGHT JOIN

RIGHT JOIN mirrors LEFT JOIN but keeps all rows from 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 RIGHT JOIN t_customer B
ON A.customer_id = B.id;

The query returns all customers; the customer with id = 2 has NULL order fields because there is no corresponding order.

4. FULL OUTER JOIN (simulated)

MySQL 5.7 does not support FULL OUTER JOIN directly, so we simulate it with a 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 (both sides)

Combine the two exclusion queries to get rows that have no counterpart in the opposite 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
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 (Cartesian product) is mentioned briefly but not detailed. A final summary diagram consolidates all join types for quick reference.

Feel free to like, comment, or ask questions about the content.