1 LEFT顽腾、INNER 和 RIGHT JOIN 原理

• LEFT JOIN：以左表為主表际乘，返回所有左表的數(shù)據(jù)
• INNER JOIN：返回兩張表的交集部分
• RIGHT JOIN：以右表為主表，返回所有右表的數(shù)據(jù)

image.png

數(shù)據(jù)準備：

# 創(chuàng)建表格 employees  并插入數(shù)據(jù)
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);

# 創(chuàng)建表格 departments 并插入數(shù)據(jù)
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);

# 創(chuàng)建表格 locations 并插入數(shù)據(jù)
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;

# 結(jié)果
+------+--------+-------+-------+----------+----------+
|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;

# 結(jié)果
+------+--------+-------+-------+----------+----------+
|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;

# 結(jié)果
+------+--------+-------+-------+----------+----------+
|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;

# 結(jié)果
+------+--------+-------+-------+----------+----------+
|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 行數(shù)據(jù)，departments 表一共 4 行數(shù)據(jù)角骤，笛卡爾積后生成 4*4=16 行數(shù)據(jù)隅忿。

# CROSS Join
SELECT * 
FROM employees
CROSS JOIN departments;

# 結(jié)果
+------+--------+-------+-------+----------+
|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.連續(xù)多個 JOIN

# 連續(xù) 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;


# 結(jié)果
+------+--------+-------+-------+----------+------------+---------+---------+
|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    |
+------+--------+-------+-------+----------+------------+---------+---------+