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

这篇具有很好参考价值的文章主要介绍了【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
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！文章来源地址https://www.toymoban.com/news/detail-850408.html

到了这里，关于【postgresql 基础入门】自然连接(natural join)与交叉连接(cross join)，等价SQL变换形式，以及它们独到的应用场景的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！