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    |
+------+--------+-------+-------+----------+------------+---------+---------+