提要:在本教程中,您将学习如何使用 PostgreSQL 的 NATURAL JOIN 从两个或多个表中查询数据。

PostgreSQL NATURAL JOIN 简介

NATURAL JOIN(自然连接)是基于连接表中的相同字段名创建的隐式连接。以下是它的语法:

SELECT *
FROM T1
NATURAL [INNER, LEFT, RIGHT] JOIN T2;

自然连接可以是 INNER JOINLEFT JOINRIGHT JOIN 等连接方式。如果没有指定连接方式,PostgreSQL 将使用默认的 INNER JOIN 方式。

如果在 SELECT 中使用了星号(*),结果将包含以下字段:

  • 所有的公共字,即两表之间相同名字的字段
  • 两表中独有的字段(即不同名字的字段)

PostgreSQL NATURAL JOIN 示例

为了演示 PostgreSQL 的自然连接(NATURAL JOIN), 我们需要创建两个表:categories(类别) 和 products(产品)。下面的 SQL 语句用于创建这两个表:

CREATE TABLE categories (
 category_id serial PRIMARY KEY,
 category_name VARCHAR (255) NOT NULL
);

CREATE TABLE products (
 product_id serial PRIMARY KEY,
 product_name VARCHAR (255) NOT NULL,
 category_id INT NOT NULL,
 FOREIGN KEY (category_id) REFERENCES category (category_id)
);

每个类别都有零个或多个产品,而每个产品只属于一个类别。products 表中的 category_id 字段是一个外键。它引用 categories 表的主键。由于两个表都有 category_id 字段,所以我们可以使用自然连接。

下面,给这两张表添加一些示例数据:

INSERT INTO categories (category_name)
VALUES
 ('Smart Phone'),
 ('Laptop'),
 ('Tablet');

INSERT INTO products (product_name, category_id)
VALUES
 ('iPhone', 1),
 ('Samsung Galaxy', 1),
 ('HP Elite', 2),
 ('Lenovo Thinkpad', 2),
 ('iPad', 3),
 ('Kindle Fire', 3);

现在,可以使用自然连接了:

SELECT
 *
FROM
 products
NATURAL JOIN categories;
 category_id | product_id |  product_name   | category_name
-------------+------------+-----------------+---------------
           1 |          1 | iPhone          | Smart Phone
           1 |          2 | Samsung Galaxy  | Smart Phone
           2 |          3 | HP Elite        | Laptop
           2 |          4 | Lenovo Thinkpad | Laptop
           3 |          5 | iPad            | Tablet
           3 |          6 | Kindle Fire     | Tablet
(6 rows)

上面的语句和下面的 INNER JOIN 等效:

SELECT
 *
FROM
 products
INNER JOIN categories USING (category_id);

自然连接的方便之处在于,它不需要手动指定连接的条件,因为它会根据同名的字段创建隐式的连接。但是,应尽可能避免使用 NATURAL JOIN,因为有时可能会导致意外的结果。

比如,在示例数据库中,city 表和 contry 表都有 contry_id 字段,于是我们写了以下自然连接的语句:

SELECT
 *
FROM
 city
NATURAL JOIN country;

结果却是空的:

 country_id | last_update | city_id | city | country
------------+-------------+---------+------+---------
(0 rows)

造成此问题的原因,请在数据库轻松学的微信公众号里输入 自然连接 获得。