【postgresql 基础入门】自然连接(natural join)与交叉连接(cross join)，等价SQL变换形式，以及它们独到的应用场景
自然连接与交叉连接
专栏内容：
（图片来源网络，侵删）
- postgresql内核源码分析
- 手写数据库toadb
- 并发编程
个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.
文章目录
- 自然连接与交叉连接
- 一、前言
- 二、概述
- 三、自然连接
- 自然连接的语法
- 案例演示
- 注意事项
- 四、交叉连接
- cross join语法
- 案例演示
- 应用场景
- 五、总结
- 六、结尾
一、前言
本文重点介绍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
如有错误或者疏漏欢迎指出，互相学习。
注：未经同意，不得转载！
- 统计分析
- 遍历尝试
- natural left/right join
- natural inner join
文章版权声明：除非注明，否则均为主机测评原创文章，转载或复制请以超链接形式并注明出处。
还没有评论，来说两句吧...