【postgresql 基础入门】自然连接(natural join)与交叉连接(cross join)，等价SQL变换形式，以及它们独到的应用场景-易微帮

自然连接与交叉连接

专栏内容：

postgresql内核源码分析

手写数据库toadb

并发编程

个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

文章目录

自然连接与交叉连接
一、前言
二、概述
三、自然连接
四、交叉连接
五、总结
六、结尾

一、前言

本文重点介绍natural join与cross join的用法，以及它们的应用场景。

二、概述

与前面介绍的left join, right join, inner join不同，本文介绍两种新的join方式，它们不需指定连接的列。

自然连接natural join，在两个表上的名称相同的列上进行连接；
交叉连接cross join，在两个表上的所有列进行连接，即表1的所有行与表2的所有行进行一次连接；

下面一起来看看它们的用法，以及分别用于那些场景中。

三、自然连接

自然连接的语法

自然连接natural join的基本语法结构如下：

SELECT select_target_list
FROM tablename1 NATURAL [INNER, LEFT, RIGHT] JOIN tablename2;

在natural join时，还可以指定连接的方式inner, left, right，如果不指定时，默认为inner方式。

等价的join写法，类似于以下语法格式：

SELECT select_target_list
FROM tablename1 [INNER, LEFT, RIGHT] JOIN tablename2 
ON tablename1.column_name1 = tablename2.column_name2;

下面通过案例演示来体会。

案例演示

作为演示的准备，首先创建两张表，并插入数据。

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10,2)
);

INSERT INTO customers (customer_id, name, email)
VALUES (1, 'John Doe', 'john@example.com'),
       (2, 'Jane Smith', 'jane@example.com'),
       (3, 'Bob Johnson', 'bob@example.com'),
       (4, 'Steven John', 'steven@example.com'),
       (5, 'Kenidy', 'Kenidy@example.com');

INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES (1, 1, '2023-01-01', 100.00),
       (2, 1, '2023-02-01', 200.00),
       (3, 2, '2023-02-15', 150.00),
       (4, 3, '2023-03-01', 75.00);

natural inner join

自然连接的inner查询，两表中都有列名customer_id，它作为关联列。

postgres=# select * from customers natural inner join orders;
 customer_id |    name     |      email       | order_id | order_date | total_amount
-------------+-------------+------------------+----------+------------+--------------
           1 | John Doe    | john@example.com |        1 | 2023-01-01 |       100.00
           1 | John Doe    | john@example.com |        2 | 2023-02-01 |       200.00
           2 | Jane Smith  | jane@example.com |        3 | 2023-02-15 |       150.00
           3 | Bob Johnson | bob@example.com  |        4 | 2023-03-01 |        75.00
(4 rows)

可以看到结果是两表的内联接。

当然查询SQL可以写作以下形式，结果也是相同的。

postgres=# select * from customers inner join orders using(customer_id);
 customer_id |    name     |      email       | order_id | order_date | total_amount
-------------+-------------+------------------+----------+------------+--------------
           1 | John Doe    | john@example.com |        1 | 2023-01-01 |       100.00
           1 | John Doe    | john@example.com |        2 | 2023-02-01 |       200.00
           2 | Jane Smith  | jane@example.com |        3 | 2023-02-15 |       150.00
           3 | Bob Johnson | bob@example.com  |        4 | 2023-03-01 |        75.00
(4 rows)

还有它的等价形式，如下：

postgres=# select * from customers inner join orders on customers.customer_id = orders.customer_id;
 customer_id |    name     |      email       | order_id | customer_id | order_date | total_amount
-------------+-------------+------------------+----------+-------------+------------+--------------
           1 | John Doe    | john@example.com |        1 |           1 | 2023-01-01 |       100.00
           1 | John Doe    | john@example.com |        2 |           1 | 2023-02-01 |       200.00
           2 | Jane Smith  | jane@example.com |        3 |           2 | 2023-02-15 |       150.00
           3 | Bob Johnson | bob@example.com  |        4 |           3 | 2023-03-01 |        75.00
(4 rows)

以上几种SQL写法查出来的结果都是一样的，它都是以内联的方式来得到结果。

natural left/right join

其它的left,right连接也是类似的，与对应的left,right join有相同的效果。


postgres=# select * from customers natural left join orders;
 customer_id |    name     |       email        | order_id | order_date | total_amount
-------------+-------------+--------------------+----------+------------+--------------
           1 | John Doe    | john@example.com   |        1 | 2023-01-01 |       100.00
           1 | John Doe    | john@example.com   |        2 | 2023-02-01 |       200.00
           2 | Jane Smith  | jane@example.com   |        3 | 2023-02-15 |       150.00
           3 | Bob Johnson | bob@example.com    |        4 | 2023-03-01 |        75.00
           5 | Kenidy      | Kenidy@example.com |          |            |
           4 | Steven John | steven@example.com |          |            |
(6 rows)

postgres=# select * from customers natural right join orders;
 customer_id |    name     |      email       | order_id | order_date | total_amount
-------------+-------------+------------------+----------+------------+--------------
           1 | John Doe    | john@example.com |        1 | 2023-01-01 |       100.00
           1 | John Doe    | john@example.com |        2 | 2023-02-01 |       200.00
           2 | Jane Smith  | jane@example.com |        3 | 2023-02-15 |       150.00
           3 | Bob Johnson | bob@example.com  |        4 | 2023-03-01 |        75.00
(4 rows)

当然也可以转换为对应的指定联接列的 join语句，这里不再赘述。

注意事项

在使用自然连接时，需要特别注意，因为它没有指定关联的列，SQL写法更加简洁，但是这也埋下了一个大坑，来看下面的两张表。

CREATE TABLE customers1 (
  customer_id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50),
  ctime timestamp default now()
);

CREATE TABLE orders1 (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10,2),
  ctime timestamp  default now()
);

在customers1与orders1表中，分别增加了一列ctime来记录创建的时间，两列的名称一样，此时两张表中有两列的名称相同。

然后上面的数据更换表名后插入，再次执行自然连接。

postgres=# select * from customers1 ;
 customer_id |    name     |       email        |           ctime
-------------+-------------+--------------------+----------------------------
           1 | John Doe    | john@example.com   | 2024-03-26 08:13:37.416833
           2 | Jane Smith  | jane@example.com   | 2024-03-26 08:13:37.416833
           3 | Bob Johnson | bob@example.com    | 2024-03-26 08:13:37.416833
           4 | Steven John | steven@example.com | 2024-03-26 08:13:37.416833
           5 | Kenidy      | Kenidy@example.com | 2024-03-26 08:13:37.416833
(5 rows)

postgres=# select * from orders1;
 order_id | customer_id | order_date | total_amount |           ctime
----------+-------------+------------+--------------+----------------------------
        1 |           1 | 2023-01-01 |       100.00 | 2024-03-26 08:13:54.054266
        2 |           1 | 2023-02-01 |       200.00 | 2024-03-26 08:13:54.054266
        3 |           2 | 2023-02-15 |       150.00 | 2024-03-26 08:13:54.054266
        4 |           3 | 2023-03-01 |        75.00 | 2024-03-26 08:13:54.054266
(4 rows)

数据与上例一致，只是多了创建本条数据的时间。

postgres=# select * from customers1 natural inner join orders1;
 customer_id | ctime | name | email | order_id | order_date | total_amount
-------------+-------+------+-------+----------+------------+--------------
(0 rows)

居然没有查到数据，这是因为连接时使用了ctime作为联接列导致，所以大家在使用时一定要注意，表中是否有多余的相同名称和类型的列存在。

四、交叉连接

交叉连接，也就是我们常说的笛卡尔积，表1的所有行与表2的每一行行进行联接，结果数据行数为两表行数的积。

下面我们通过语法格式，案例演示，应用场景三个部分来详细介绍交叉连接。

cross join语法

SELECT 
  select_target_list 
FROM tablename1 CROSS JOIN tablename2;

在交叉连接中，不用指定inner,left,right，其实它不需要有联接列，而是将两表的两行数据直接拼接起来组成结果集中的新行。

等价的SQL写法如下：

SELECT 
  select_target_list 
FROM tablename1, tablename2;

案例演示

演示的表和数据仍然使用上例中的customers与orders表。

cross join的写法的SQL执行结果如下：

postgres=# select * from customers cross join orders ;
 customer_id |    name     |       email        | order_id | customer_id | order_date | total_amount
-------------+-------------+--------------------+----------+-------------+------------+--------------
           1 | John Doe    | john@example.com   |        1 |           1 | 2023-01-01 |       100.00
           2 | Jane Smith  | jane@example.com   |        1 |           1 | 2023-01-01 |       100.00
           3 | Bob Johnson | bob@example.com    |        1 |           1 | 2023-01-01 |       100.00
           4 | Steven John | steven@example.com |        1 |           1 | 2023-01-01 |       100.00
           5 | Kenidy      | Kenidy@example.com |        1 |           1 | 2023-01-01 |       100.00
           1 | John Doe    | john@example.com   |        2 |           1 | 2023-02-01 |       200.00
           2 | Jane Smith  | jane@example.com   |        2 |           1 | 2023-02-01 |       200.00
           3 | Bob Johnson | bob@example.com    |        2 |           1 | 2023-02-01 |       200.00
           4 | Steven John | steven@example.com |        2 |           1 | 2023-02-01 |       200.00
           5 | Kenidy      | Kenidy@example.com |        2 |           1 | 2023-02-01 |       200.00
           1 | John Doe    | john@example.com   |        3 |           2 | 2023-02-15 |       150.00
           2 | Jane Smith  | jane@example.com   |        3 |           2 | 2023-02-15 |       150.00
           3 | Bob Johnson | bob@example.com    |        3 |           2 | 2023-02-15 |       150.00
           4 | Steven John | steven@example.com |        3 |           2 | 2023-02-15 |       150.00
           5 | Kenidy      | Kenidy@example.com |        3 |           2 | 2023-02-15 |       150.00
           1 | John Doe    | john@example.com   |        4 |           3 | 2023-03-01 |        75.00
           2 | Jane Smith  | jane@example.com   |        4 |           3 | 2023-03-01 |        75.00
           3 | Bob Johnson | bob@example.com    |        4 |           3 | 2023-03-01 |        75.00
           4 | Steven John | steven@example.com |        4 |           3 | 2023-03-01 |        75.00
           5 | Kenidy      | Kenidy@example.com |        4 |           3 | 2023-03-01 |        75.00
(20 rows)

可以看到结果集总共有20行数据，是两表的行数积，同时结果集中的列是两表所有列的拼接。

它的等价写法SQL的执行结果如下：

postgres=# select * from customers , orders ;
 customer_id |    name     |       email        | order_id | customer_id | order_date | total_amount
-------------+-------------+--------------------+----------+-------------+------------+--------------
           1 | John Doe    | john@example.com   |        1 |           1 | 2023-01-01 |       100.00
           2 | Jane Smith  | jane@example.com   |        1 |           1 | 2023-01-01 |       100.00
           3 | Bob Johnson | bob@example.com    |        1 |           1 | 2023-01-01 |       100.00
           4 | Steven John | steven@example.com |        1 |           1 | 2023-01-01 |       100.00
           5 | Kenidy      | Kenidy@example.com |        1 |           1 | 2023-01-01 |       100.00
           1 | John Doe    | john@example.com   |        2 |           1 | 2023-02-01 |       200.00
           2 | Jane Smith  | jane@example.com   |        2 |           1 | 2023-02-01 |       200.00
           3 | Bob Johnson | bob@example.com    |        2 |           1 | 2023-02-01 |       200.00
           4 | Steven John | steven@example.com |        2 |           1 | 2023-02-01 |       200.00
           5 | Kenidy      | Kenidy@example.com |        2 |           1 | 2023-02-01 |       200.00
           1 | John Doe    | john@example.com   |        3 |           2 | 2023-02-15 |       150.00
           2 | Jane Smith  | jane@example.com   |        3 |           2 | 2023-02-15 |       150.00
           3 | Bob Johnson | bob@example.com    |        3 |           2 | 2023-02-15 |       150.00
           4 | Steven John | steven@example.com |        3 |           2 | 2023-02-15 |       150.00
           5 | Kenidy      | Kenidy@example.com |        3 |           2 | 2023-02-15 |       150.00
           1 | John Doe    | john@example.com   |        4 |           3 | 2023-03-01 |        75.00
           2 | Jane Smith  | jane@example.com   |        4 |           3 | 2023-03-01 |        75.00
           3 | Bob Johnson | bob@example.com    |        4 |           3 | 2023-03-01 |        75.00
           4 | Steven John | steven@example.com |        4 |           3 | 2023-03-01 |        75.00
           5 | Kenidy      | Kenidy@example.com |        4 |           3 | 2023-03-01 |        75.00
(20 rows)

结果是相同的。

当然还有一种等价用法，利用inner join的条件表达式恒为true的方式，也可以达到cross join的效果。

postgres=# select * from customers inner join  orders on true;
 customer_id |    name     |       email        | order_id | customer_id | order_date | total_amount
-------------+-------------+--------------------+----------+-------------+------------+--------------
           1 | John Doe    | john@example.com   |        1 |           1 | 2023-01-01 |       100.00
           2 | Jane Smith  | jane@example.com   |        1 |           1 | 2023-01-01 |       100.00
           3 | Bob Johnson | bob@example.com    |        1 |           1 | 2023-01-01 |       100.00
           4 | Steven John | steven@example.com |        1 |           1 | 2023-01-01 |       100.00
           5 | Kenidy      | Kenidy@example.com |        1 |           1 | 2023-01-01 |       100.00
           1 | John Doe    | john@example.com   |        2 |           1 | 2023-02-01 |       200.00
           2 | Jane Smith  | jane@example.com   |        2 |           1 | 2023-02-01 |       200.00
           3 | Bob Johnson | bob@example.com    |        2 |           1 | 2023-02-01 |       200.00
           4 | Steven John | steven@example.com |        2 |           1 | 2023-02-01 |       200.00
           5 | Kenidy      | Kenidy@example.com |        2 |           1 | 2023-02-01 |       200.00
           1 | John Doe    | john@example.com   |        3 |           2 | 2023-02-15 |       150.00
           2 | Jane Smith  | jane@example.com   |        3 |           2 | 2023-02-15 |       150.00
           3 | Bob Johnson | bob@example.com    |        3 |           2 | 2023-02-15 |       150.00
           4 | Steven John | steven@example.com |        3 |           2 | 2023-02-15 |       150.00
           5 | Kenidy      | Kenidy@example.com |        3 |           2 | 2023-02-15 |       150.00
           1 | John Doe    | john@example.com   |        4 |           3 | 2023-03-01 |        75.00
           2 | Jane Smith  | jane@example.com   |        4 |           3 | 2023-03-01 |        75.00
           3 | Bob Johnson | bob@example.com    |        4 |           3 | 2023-03-01 |        75.00
           4 | Steven John | steven@example.com |        4 |           3 | 2023-03-01 |        75.00
           5 | Kenidy      | Kenidy@example.com |        4 |           3 | 2023-03-01 |        75.00
(20 rows)

应用场景

cross join来作两表的笛卡尔积，在一些场景下是非常有帮助的。

遍历尝试

比如任务分配，那种任务与人员组合更合适，可能需要一一对应分析一下；还有一些计算推理，用到了枚举的方式，那就对所有可能结果都需要分析；

此时分析任务的输入或者驱动就是cross join的结果集。

统计分析

假如要对所有门店与所有商品进行统计分析，看门店销售的情况，那么也需要作一个cross join，即使某个商品在该门店没有销售订单，也要有分析结果。

五、总结

本文分享了natural join和cross join的语法格式，同时它们有几种等价的SQL写法；因为natural join简洁的写法，有时会带来麻烦，在使用前要检查两表中是否有多列会产生join效果。

cross join虽然简单，但是它也有很多应用场景，在统计分析，任务分析等方面经常使用。

六、结尾

非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！

本文含有隐藏内容，请开通VIP 后查看

【postgresql 基础入门】自然连接(natural join)与交叉连接(cross join)，等价SQL变换形式，以及它们独到的应用场景

自然连接与交叉连接

文章目录

一、前言

二、概述

三、自然连接

自然连接的语法

案例演示

注意事项

四、交叉连接

cross join语法

案例演示

应用场景

五、总结

六、结尾

网站公告

今日签到

热门文章

最新发布