Difference between natural join and inner join

最新推荐文章于 2024-01-04 14:00:00 发布

weixin_33674976

最新推荐文章于 2024-01-04 14:00:00 发布

阅读量83

点赞数

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/2275100/blog/847376

本文详细对比了SQL中INNER JOIN与NATURAL JOIN的不同之处，通过具体实例展示了两者在返回列数量上的差异，并解释了NATURAL JOIN如何避免重复列。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned.

Consider:

TableA                            TableB
Column1    Column2                Column1    Column3
1          2                      1          3

The INNER JOIN of TableA and TableB on Column1 will return

a.column1  a.column2  b.column1  b.column3
1          2          1          3

SELECT * FROM TableA INNER JOIN TableB USING (Column1)
SELECT * FROM TableA INNER JOIN TableB ON TableA.Column1 = TableB.Column1

The NATURAL JOIN of TableA and TableB on Column1 will return:

column1  column2  column3
1        2        3

SELECT * FROM TableA NATURAL JOIN TableB

The repeated column is avoided.

(AFAICT from the standard grammar, you can't specify the joining columns in a natural join; the join is strictly name-based. See also Wikipedia.)

(There's a cheat in the inner join output; the a. and b. parts would not be in the column names; you'd just have column1, column2, column1, column3 as the headings.)

转载于:https://my.oschina.net/u/2275100/blog/847376