了解SQL中的自然联接 [英] Understanding Natural Join in SQL

问题描述

因此，我对SQL还是相当陌生，目前我坚持使用自然联接的概念.

So I am fairly new to SQL and currently I am stuck with the concept of Natural Join.

目前，我了解自然联接运算符通过匹配具有相同名称的所有列并丢弃重复的列并丢弃不匹配的行来联接表.

Currently I understand that the Natural Join operator joins tables by matching all columns with the same names and discarding duplicate columns and discarding rows which do not match.

所以最近我遇到一个问题，确实很基本，但是我无法解决这个问题.

So recently I came across a question, pretty basic really, however i couldn't wrap my head around it.

所以有两个关系R(A，B，C)和S(A，B，D)

So there are 2 relations R(A,B,C) and S(A,B,D)

    A B C           A B D
    6 8 7           5 8 7
    6 6 7           6 6 7
    7 8 6           6 8 6

以下查询会产生多少行?

How many rows would the following query produce?

选择*来自R NATURAL JOIN S

SELECT * FROM R NATURAL JOIN S

因此，我立即看到两列匹配"它们是A和B.通过使用自然连接，它将同时考虑A和B或仅考虑A，因此将丢弃哪些信息.

So instantly I see two columns which 'match' they being A and B. By using Natural Join would it take into account both A and B or just A and as such, what information would be discarded.

答案是2行.有人可以解释获得两行的方法吗?

The answer is 2 rows. Can someone please explain the method of getting 2 rows?

谢谢！

是否有2行是

    A B D
    6 6 7
    6 6 6

如果是，我可以删除这个问题

If so, I may delete this question

推荐答案

这个评论太长了.不要使用自然联接.不要理会自然加入.他们是可憎的.

This is too long for a comment. Don't use natural joins. Don't bother learning natural joins. They are an abomination.

为什么?连接条件基于具有相同名称的列.自然联接甚至不考虑已声明的外键关系.这可能非常危险.或者-就我而言-因为几乎我的所有表都有CreatedAt和CreatedBy，所以它们还是没用的.

Why? The join conditions are based on columns with the same names. Natural joins do not even take declared foreign key relationships into account. This can be quite dangerous. Or -- in my case -- because almost all my tables have CreatedAt and CreatedBy, they are useless anyway.

相反，列出join键.对于您的情况(因为您有select *)，using子句最合适:

Instead, list the join keys. In your case (because you have select *), the using clause is most appropriate:

SELECT * 
FROM R JOIN
     S
     USING (A, B);

这还具有在查询中显式显示键的优点，这大大减小了错误的范围.

This also has the advantage that the keys are explicitly shown in the query, which greatly reduces the scope for errors.

这篇关于了解SQL中的自然联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！