提要:本教程你将学习到如何通过使用 PostgreSQL 的 INNER JOIN 来从多个表里查询数据。

PostgreSQL INNER JOIN 简介

到目前为止,您已经学会了如何从表中查询数据、选择所需的列和行,以及如何按特定顺序对结果集进行排序

现在是时候转移到数据库中最重要的概念之一,称为“连接”,它允许您将一个表中的数据与其他表中的数据相关联。数据表之间有多种连接方式: INNER JOIN(内连)、OUTER JOIN(外连)和自连接(self-join)。本节的讲述的重点是 INNER JOIN

假设有 AB 两张表。B 表通过 fka 字段与 A 的主键进行关联。

A表和B表

SELECT 语句中使用 INNER JOIN 子句可以从两张表里同时获取数据:

SELECT
 A.pka,
 A.c1,
 B.pkb,
 B.c2
FROM
 A
INNER JOIN B 
ON A.pka = B.fka;

连接 A 表和 B 表:

  • 首先,在 SELECT 中指定要从两张表里获取的字段,本例中的 A.pka, A.c1, B.pkb,B.c2
  • 然后,在 FROM 子句中指定主表,本例中的 A
  • 最后,指定哪张表和主表进行连接。指定方式是通过 INNER JOIN 子句实现的,本例中的 B 即是。另外,还要通过 ON 关键字来说明两张表连接的条件,本例中的 A.pka = B.fka

PostgreSQL 会在 A 表的每一行数据里扫描 B 表里是否有匹配的数据。匹配的依据就是 ON 指定的条件,比如上例的 A.pka = B.fka。如果扫描到了匹配的数据,它会把两张表里的字段合并成一行,然后作为结果集进行返回。

主键字段(pka)和外键字段(fka)通常都会建立索引;这样 PostgreSQL 只需要在索引里进行快速匹配。

有时候,A 表和 B 可能存在同名的字段,所以,我们需要使用 表名.字段名 的格式来显式地引用字段。当然,表名可能很长,这时候可以使用表的别名来简化,比如,使用 t 作为别名,然后可以这样引用字段 t.字段名

下图显示了 INNER JOIN 是如何工作的:

INNER-JOIN工作示意图

INNER JOIN 返回所有在 A 表和 B 表同时存在的数据。就是说,只有两张表里都存在的对应数据,才会返回。

PostgreSQL INNER JOIN 示例

两张表的内连示例

看一下示例数据库中,customer 表和 payment 表的结构和关系:

customer和payment

每个客户有零个或多个支付。每个支付都属于并且只属于一个客户。customer_id 将两张表关联起来了。

你可以使用下面的语句将 customerpayment 进行 INNER JOIN

SELECT
 customer.customer_id,
 first_name,
 last_name,
 email,
 amount,
 payment_date
FROM
 customer
INNER JOIN payment ON payment.customer_id = customer.customer_id;

customer和payment的内连

可以像下例这样,添加依据 customer_id 进行排序:

SELECT
 customer.customer_id,
 first_name,
 last_name,
 email,
 amount,
 payment_date
FROM
 customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
ORDER BY
 customer.customer_id;

customer和payment的内连,依据customer_id进行排序

还可以使用 WHERE 对客户进行筛选。下例筛选了 customer_id 为 2 的客户:

SELECT
 customer.customer_id,
 first_name,
 last_name,
 email,
 amount,
 payment_date
FROM
 customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
WHERE
 customer.customer_id = 2;

customer和payment的内连,依据customer_id进行筛选

三张表的内连示例

下图展示了 customerpayment 表和 staff表的关系。

customer-payment-staff-tables

  • 每个员工(staff)有零或多个支付信息(payment)。即,每个支付和一个且只有一个员工对应。
  • 每个客户(customer)有零或多个支付。即,每个支付属于且只属于一个客户。

要在三个表之间创建 INNER JOIN,只需要将第二个 INNER JOIN 放在第一个 INNER JOIN 后面,如下所示:

SELECT
 customer.customer_id,
 customer.first_name customer_first_name,
 customer.last_name customer_last_name,
 customer.email,
 staff.first_name staff_first_name,
 staff.last_name staff_last_name,
 amount,
 payment_date
FROM
 customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
INNER JOIN staff ON payment.staff_id = staff.staff_id;

三表内连

利用相同的方法,你可以很容易的将更多的表进行内连。

相关主题

上一篇:PostgreSQL的BETWEEN