SQL 三种 JOIN 怎么选:两张小表跑一遍,RIGHT JOIN 在 SQLite 上其实能跑
1. 先说结论:三种 JOIN 保留谁
今天在 DevSpace 问了 SQL 的三种 JOIN,AI 给了两张小表和三张结果,我又在本机 SQLite 3.45.1 上跑了一遍。结论很简单:
| 类型 | 保留谁 | 一句话 |
|---|---|---|
| INNER JOIN | 只保留两边都能匹配上的行 | 交集 |
| LEFT JOIN | 左表全保留,右表匹配不上补 NULL | 左表不丢人 |
| RIGHT JOIN | 右表全保留,左表匹配不上补 NULL | 右表不丢人 |
关键在 ON 后面的条件:ON e.dept_id = d.id,左表是 FROM 后面的 employee,右表是 JOIN 后面的 department。左、右是按你写 SQL 时两张表的位置定的,不是按数据大小。
2. 两张小表:故意留两处“对不上”
CREATE TABLE employee (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER);
CREATE TABLE department (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO employee VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', 99);
INSERT INTO department VALUES (10, 'Engineering'), (20, 'Sales'), (30, 'HR');
employee 表:
| id | name | dept_id |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | 99 |
department 表:
| id | name |
|---|---|
| 10 | Engineering |
| 20 | Sales |
| 30 | HR |
Charlie 的 dept_id 是 99,部门表里没有 99 号部门;HR 部门没有员工。这两处“对不上”,就是三种 JOIN 差别露出来的地方。
3. 实测一:INNER JOIN 只留交集
SELECT e.name, e.dept_id, d.name
FROM employee e INNER JOIN department d ON e.dept_id = d.id;
实测输出(本机 SQLite 3.45.1):
('Alice', 10, 'Engineering')
('Bob', 20, 'Sales')
Charlie 丢了(99 号部门不存在),HR 也没出现(没有员工)。只保留两边都能匹配上的行。
4. 实测二:LEFT JOIN 左表不丢人
SELECT e.name, e.dept_id, d.name
FROM employee e LEFT JOIN department d ON e.dept_id = d.id;
实测输出:
('Alice', 10, 'Engineering')
('Bob', 20, 'Sales')
('Charlie', 99, None)
左表 employee 的每一行都保留了。Charlie 保留了,但部门列是 NULL,因为没有部门能跟他匹配。典型场景:查“所有员工和各自的部门”,没部门的员工也要列出来。
5. 实测三:RIGHT JOIN,AI 说错了一处
AI 的回答里说“SQLite 这个版本不支持 RIGHT JOIN”,然后用换表写 LEFT JOIN 的等价写法跑了结果。这句不准。
SQLite 从 3.39.0(2022 年 6 月)起就支持 RIGHT JOIN 和 FULL OUTER JOIN。本机 SQLite 3.45.1,直接写 RIGHT JOIN 就能跑:
SELECT e.name, e.dept_id, d.name
FROM employee e RIGHT JOIN department d ON e.dept_id = d.id;
实测输出:
('Alice', 10, 'Engineering')
('Bob', 20, 'Sales')
(None, None, 'HR')
最后一行 emp 是 NULL、部门是 HR:HR 部门保留了,但没有员工属于它。
AI 给的等价写法(把表顺序反过来写 LEFT JOIN)结果完全一样:
SELECT e.name, d.name
FROM department d -- 主体放左边
LEFT JOIN employee e ON e.dept_id = d.id;
('Alice', 10, 'Engineering')
('Bob', 20, 'Sales')
(None, None, 'HR')
所以结论是:RIGHT JOIN 不是“SQLite 不支持”,是老版本不支持;新版本直接写就行,换表 LEFT JOIN 是等价写法,两个结果一字不差。写稿时别照抄 AI 的版本说法,先查一下你用的 SQLite 版本:
import sqlite3
print(sqlite3.sqlite_version) # 本机:3.45.1
6. 选哪个:速查表
| 场景 | 用哪个 |
|---|---|
| 只要两边都有的记录,比如“有部门的员工” | INNER JOIN |
| 左表一条不能少,比如“所有员工,没部门的也列出来” | LEFT JOIN |
| 右表一条不能少,比如“所有部门,没员工的也列出来” | RIGHT JOIN |
| 两边都不能少 | FULL OUTER JOIN(MySQL 不支持,用 UNION 拼) |
经验法则:想清楚哪张表的数据一条都不能少,就把它放在对应的位置。实际写 SQL 时,RIGHT JOIN 很少直接写,大多数人习惯把要保留的表放左边、统一用 LEFT JOIN——同一个结果,读起来顺。
7. 完整可运行脚本
上面几段拼成的完整脚本,直接 python3 join_demo.py 就能跑:
"""
SQL 三种 JOIN 对比 —— INNER / LEFT / RIGHT
直接运行: python3 join_demo.py
"""
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE employee (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER)")
cur.execute("CREATE TABLE department (id INTEGER PRIMARY KEY, name TEXT)")
cur.executemany(
"INSERT INTO employee VALUES (?,?,?)",
[(1, "Alice", 10), (2, "Bob", 20), (3, "Charlie", 99)],
)
cur.executemany(
"INSERT INTO department VALUES (?,?)",
[(10, "Engineering"), (20, "Sales"), (30, "HR")],
)
print("--- INNER JOIN ---")
for r in cur.execute(
"SELECT e.name, e.dept_id, d.name FROM employee e "
"INNER JOIN department d ON e.dept_id = d.id"
):
print(r)
print("--- LEFT JOIN ---")
for r in cur.execute(
"SELECT e.name, e.dept_id, d.name FROM employee e "
"LEFT JOIN department d ON e.dept_id = d.id"
):
print(r)
print("--- RIGHT JOIN ---")
for r in cur.execute(
"SELECT e.name, e.dept_id, d.name FROM employee e "
"RIGHT JOIN department d ON e.dept_id = d.id"
):
print(r)
print("--- RIGHT JOIN 等价写法:换表 LEFT JOIN ---")
for r in cur.execute(
"SELECT e.name, e.dept_id, d.name FROM department d "
"LEFT JOIN employee e ON e.dept_id = d.id"
):
print(r)
print("sqlite version:", sqlite3.sqlite_version)
记住两点就行:INNER 是交集,LEFT 是“左表不丢人”,RIGHT 是“右表不丢人”;再就是别信“SQLite 不支持 RIGHT JOIN”这种老说法,3.39 起就支持了,跑之前先看一眼你的 SQLite 版本。
- 点赞
- 收藏
- 关注作者
评论(0)