1 LEFT、INNER 和 RIGHT JOIN 原理
- LEFT JOIN：以左表为主表，返回所有左表的数据
- INNER JOIN：返回两张表的交集部分
- RIGHT JOIN：以右表为主表，返回所有右表的数据
image.png
数据准备：
# 创建表格 employees 并插入数据
CREATE TABLE employees (
emp_id INT,
emp_name STRING,
dept_id INT
);
INSERT INTO employees VALUES
(1, 'John', 100),
(2, 'Mike', 200),
(3, 'Lisa', 100),
(4, 'Sarah', 300);
# 创建表格 departments 并插入数据
CREATE TABLE departments (
dept_id INT,
dept_name STRING,
location_id INT
);
INSERT INTO departments VALUES
(100, 'IT', 1),
(200, 'Finance', 3),
(300, 'HR', 1),
(400, 'Marketing', 2);
# 创建表格 locations 并插入数据
CREATE TABLE locations (
location_id INT,
location STRING
);
INSERT INTO locations VALUES
(1, 'New York'),
(2, 'London'),
(3, 'Paris');
1.1 LEFT JOIN 示例
# Left Join
SELECT *
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id;
# 结果
+------+--------+-------+-------+----------+----------+
|emp_id|emp_name|dept_id|dept_id|dept_name |location_id |
+------+--------+-------+-------+----------+----------+
|1 |John |100 |100 |IT |1 |
|2 |Mike |200 |200 |Finance |3 |
|3 |Lisa |100 |100 |IT |1 |
|4 |Sarah |300 |300 |HR |1 |
+------+--------+-------+-------+----------+----------+
1.2 INNER JOIN 示例
# INNER Join
SELECT *
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;
# 结果
+------+--------+-------+-------+----------+----------+
|emp_id|emp_name|dept_id|dept_id|dept_name |location_id |
+------+--------+-------+-------+----------+----------+
|1 |John |100 |100 |IT |1 |
|3 |Lisa |100 |100 |IT |1 |
|2 |Mike |200 |200 |Finance |3 |
|4 |Sarah |300 |300 |HR |1 |
+------+--------+-------+-------+----------+----------+
1.3 RIGHT JOIN 示例
# RIGHT Join
SELECT *
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.dept_id;
# 结果
+------+--------+-------+-------+----------+----------+
|emp_id|emp_name|dept_id|dept_id|dept_name |location_id |
+------+--------+-------+-------+----------+----------+
|1 |John |100 |100 |IT |1 |
|3 |Lisa |100 |100 |IT |1 |
|2 |Mike |200 |200 |Finance |3 |
|4 |Sarah |300 |300 |HR |2 |
|NULL |NULL |NULL |400 |Marketing |2 |
+------+--------+-------+-------+----------+----------+
2.FULL OUTER、UNION ALL 和 CROSS JOIN 原理
- FULL OUTER JOIN：全外连接，返回两张表的并集
- CROSS JOIN：返回的是两张表的笛卡尔积
image.png
2.1 FULL OUTER JOIN 示例
# FULL OUTER Join
SELECT *
FROM employees
FULL OUTER JOIN departments ON employees.dept_id = departments.dept_id;
# 结果
+------+--------+-------+-------+----------+----------+
|emp_id|emp_name|dept_id|dept_id|dept_name |location_id |
+------+--------+-------+-------+----------+----------+
|1 |John |100 |100 |IT |1 |
|3 |Lisa |100 |100 |IT |1 |
|2 |Mike |200 |200 |Finance |3 |
|4 |Sarah |300 |300 |HR |1 |
|NULL |NULL |NULL |400 |Marketing |2 |
+------+--------+-------+-------+----------+----------+
2.2 CROSS JOIN 示例
说明：employees 表一共 4 行数据，departments 表一共 4 行数据，笛卡尔积后生成 4*4=16 行数据。
# CROSS Join
SELECT *
FROM employees
CROSS JOIN departments;
# 结果
+------+--------+-------+-------+----------+
|emp_id|emp_name|dept_id|dept_id|dept_name |
+------+--------+-------+-------+----------+
|1 |John |100 |100 |IT |1 |
|1 |John |100 |200 |Finance |3 |
|1 |John |100 |300 |HR |1 |
|1 |John |100 |400 |Marketing |2 |
|2 |Mike |200 |100 |IT |1 |
|2 |Mike |200 |200 |Finance |3 |
|2 |Mike |200 |300 |HR |1 |
|2 |Mike |200 |400 |Marketing |2 |
|3 |Lisa |100 |100 |IT |1 |
|3 |Lisa |100 |200 |Finance |3 |
|3 |Lisa |100 |300 |HR |1 |
|3 |Lisa |100 |400 |Marketing |2 |
|4 |Sarah |300 |100 |IT |1 |
|4 |Sarah |300 |200 |Finance |3 |
|4 |Sarah |300 |300 |HR |1 |
|4 |Sarah |300 |400 |Marketing |2 |
+------+--------+-------+-------+----------+
3.连续多个 JOIN
# 连续 3 个表的 Join
SELECT *
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN locations l ON d.location_id = l.location_id;
# 结果
+------+--------+-------+-------+----------+------------+---------+---------+
|emp_id|emp_name|dept_id|dept_id|dept_name |location_id |location_id |location |
+------+--------+-------+-------+----------+------------+---------+---------+
|1 |John |100 |100 |IT |1 |1 |New York |
|2 |Mike |200 |200 |Finance |3 |3 |Paris |
|3 |Lisa |100 |100 |IT |1 |1 |New York |
|4 |Sarah |300 |300 |HR |1 |1 |New York |
+------+--------+-------+-------+----------+------------+---------+---------+
