连接

PostgreSQL快速入门
140
2023/07/29 14:31:56
postgresql

本章将讨论 PostgreSQL 的各种连接：内连接、左外连接、右外连接、交叉连接、自然连接、自连接和完全外连接。值得一提的是，哪怕到了 MySQL 8，MySQL 依然不支持完全外连接。

开始之前，先创建两张表并插入一些数据：

CREATE TABLE basket_a (
    a INT PRIMARY KEY,
    fruit_a VARCHAR (100) NOT NULL
);

CREATE TABLE basket_b (
    b INT PRIMARY KEY,
    fruit_b VARCHAR (100) NOT NULL
);

INSERT INTO basket_a (a, fruit_a)
VALUES
    (1, 'Apple'),
    (2, 'Orange'),
    (3, 'Banana'),
    (4, 'Cucumber');

INSERT INTO basket_b (b, fruit_b)
VALUES
    (1, 'Orange'),
    (2, 'Apple'),
    (3, 'Watermelon'),
    (4, 'Pear');

内连接

  • 使用 INNER JOIN 进行连接，使用 ON 指定连接条件
  • 内连接检查第一个表(basket_a)中每一行，并与第二个表(basket_b)的每一行进行对比：如果指定字段的值相等（由 ON 指定的 fruit_a 和 fruit_b），则创建新行，并将该行添加到结果集中。

左外连接

  • 使用 LEFT JOIN 进行连接，使用 ON 指定条件
    • LEFT JOIN 左侧的表为“左表”，此例中的 basket_a
    • LEFT JOIN 右侧的表为“右表”，此例中的 basket_b
  • 从左表（basket_a）中选择数据，并将它的 fruit_a字段的值和右表（basket_b）的 fruit_b 字段值进行对比（由 ON 指定）：
    • 如果相等，将创建一个包含两张表记录的新行，并将新行添加到结果集中
    • 如果不相等，只创建只包含左表记录，右边记录全部为 NULL 的新行，并将新行添加到结果集中

右外连接

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
RIGHT JOIN basket_b ON fruit_a = fruit_b;
  • 和左外连接原理相同
  • 只是以右表的记录为准，对右表选择数据，如果左表有相等的记录则包含左表数据；反则则只包含右表数据，左表为 NULL

全外连接

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
FULL OUTER JOIN basket_b 
    ON fruit_a = fruit_b;
  • 返回两张表中所有记录
  • 如果两张表有符合条件的记录，则返回两张表的记录
  • 如果有一张表没有符合条件的记录，则以 NULL 填充

交叉连接

生成多个表中，所有记录的笛卡尔积。

  • 没有 ON 指定条件
  • 这是一个非常耗费资源的操作
SELECT 字段列表 FROM t1 CROSS JOIN t1;

-- 等价于
SELECT 字段列表 FROM t1,t2;

-- 使用INNER JOIN模拟：ON的条件始终为真
SELECT 字段列表 FROM t1 INNER JOIN t2 ON 1=1;

自然连接

基于连接表中，相同字段名创建的隐式连接。

SELECT 字段列表 FROM t1 NATURAL [INNER, LEFT, RIGHT] JOIN t2;

比如，示例数据库中，city表和country 表都有 country_id 字段 ：

SELECT * FROM city NATURAL INNER JOIN country;
  • 预期将隐式使用两个表中的同名字段 country_id 进行连接
  • 实际结果确是空结果，原因是两个表中还有一个同名字段 last_update

自连接

所谓自连接，是指连接两方都是同一张表，常用应用场景是父子分类。

SELECT FROM 字段列表 FROM 表 AS t1 INNER JOIN 表 AS t2 ON t1.字段名1 = t2.字段名2;

示例：

CREATE TABLE employee (
	employee_id INT PRIMARY KEY,
	first_name VARCHAR (255) NOT NULL,
	last_name VARCHAR (255) NOT NULL,
	manager_id INT,
	FOREIGN KEY (manager_id) 
	REFERENCES employee (employee_id) 
	ON DELETE CASCADE
);
INSERT INTO employee (
	employee_id,
	first_name,
	last_name,
	manager_id
)
VALUES
	(1, 'Windy', 'Hays', NULL),
	(2, 'Ava', 'Christensen', 1),
	(3, 'Hassan', 'Conner', 1),
	(4, 'Anna', 'Reeves', 2),
	(5, 'Sau', 'Norman', 2),
	(6, 'Kelsie', 'Hays', 3),
	(7, 'Tory', 'Goff', 3),
	(8, 'Salley', 'Lester', 3);
	
SELECT
    e.first_name || ' ' || e.last_name AS employee,
    m.first_name || ' ' || m.last_name AS manager
FROM
    employee AS e
INNER JOIN employee AS m ON m.employee_id = e.manager_id
ORDER BY manager;