MySQL 连接的使用
INNER / LEFT / RIGHT JOIN 的语义与写法。
Key PointsJOIN 把多张表按关联键拼成一张宽表；LEFT JOIN 保留左表全部行。
准备两张表
SQL
CREATE TABLE orders (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
amount DECIMAL(10,2) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
INSERT INTO orders (user_id, amount) VALUES (1, 99.00), (1, 12.50), (3, 5.00);Output
Query OK, 3 rows affected (0.01 sec)INNER JOIN
SQL
SELECT u.username, o.amount
FROM users u
INNER JOIN orders o ON o.user_id = u.id;Output
+----------+--------+
| username | amount |
+----------+--------+
| alice | 99.00 |
| alice | 12.50 |
| carol | 5.00 |
+----------+--------+LEFT JOIN
SQL
-- 找出「没有下过单」的用户：右表为 NULL 即代表没匹配
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;Output
+----------+----------+
| username | order_id |
+----------+----------+
| alice | 1 |
| bob | NULL |
| carol | 3 |
+----------+----------+Put join conditions in
ON and filters in WHERE. With LEFT JOIN, to keep only unmatched rows, filter WHERE right.pk IS NULL — putting it in ON won't drop left rows.准备两张表
SQL
CREATE TABLE orders (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
amount DECIMAL(10,2) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
INSERT INTO orders (user_id, amount) VALUES (1, 99.00), (1, 12.50), (3, 5.00);Output
Query OK, 3 rows affected (0.01 sec)INNER JOIN
SQL
SELECT u.username, o.amount
FROM users u
INNER JOIN orders o ON o.user_id = u.id;Output
+----------+--------+
| username | amount |
+----------+--------+
| alice | 99.00 |
| alice | 12.50 |
| carol | 5.00 |
+----------+--------+LEFT JOIN
SQL
-- 找出「没有下过单」的用户：右表为 NULL 即代表没匹配
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;Output
+----------+----------+
| username | order_id |
+----------+----------+
| alice | 1 |
| bob | NULL |
| carol | 3 |
+----------+----------+ON 写连接条件、WHERE 写过滤条件，二者别混。LEFT JOIN 后若要「只要没匹配的行」，过滤要写在 WHERE 且判断右表主键 IS NULL，写在 ON 里不会过滤左表行。