sql 连接为维恩图 [英] sql joins as venn diagram

问题描述

我在理解 sql 中的连接时遇到了麻烦，并发现了这张我认为可能对我有帮助的图片.问题是我不完全理解它.例如，图像右上角的连接，将整个 B 圆圈涂成红色，但仅与 A 重叠.图像使圆形 B 看起来是 sql 语句的主要焦点，但 sql 语句本身，从 A 开始(从 A 中选择，加入 B)，给我传达了相反的印象，即 A 将成为 sql 语句的焦点.

同理，下图只包含了B圈的数据，那么为什么join语句中根本就包含了A呢?

问题:从右上方顺时针方向到中心结束，谁能提供更多关于每个sql图像表示的信息，解释

a) 为什么在每种情况下都需要连接(例如，特别是在没有从 A 或 B 中获取数据的情况下，即只有 A 或 B 但不是两者都有颜色)

b) 和任何其他细节，可以阐明为什么图像是 sql 的良好表示

解决方案

我认为您的主要潜在困惑是，当(例如)只有 A 以红色突出显示时，您将其视为意思是查询只从 A 返回数据"，但实际上它的意思是查询只返回数据对于那些A> 有记录".查询可能仍包含来自 B 的数据.(对于 B 没有没有记录的情况，查询将替换 NULL.)<块引用>

同理，下图只包含了B圈的数据，那么为什么join语句中根本就包含了A呢?

如果你的意思是 —A全白的图像，B与A不重叠的部分有一个红新月形，那么:A 出现在查询中的原因是，A 是它如何在B 中找到需要排除的记录.(如果A没有出现在查询中，那么维恩图就不会有A，它只会显示B，还有'd 无法区分所需的记录和不需要的记录.)

<块引用>

这张图片看起来好像圆圈 B 是 sql 语句的主要焦点，但 sql 语句本身以 A 开头(从 A 中选择，加入 B)给我传达了相反的印象，即 A 将成为sql语句的焦点.

说的很对.因此，RIGHT JOIN 相对不常见；尽管使用 LEFT JOIN 的查询几乎总是可以重新排序以使用 RIGHT JOIN 代替(反之亦然)，但通常人们会使用 编写查询>LEFT JOIN 而不是 RIGHT JOIN.

I've had trouble understanding joins in sql and came upon this image which I think might help me. The problem is that I don't fully understand it. For example, the join in the top right corner of the image, which colors the full B circle red and but only the overlap from A. The image makes it seem like circle B is the primary focus of the sql statement, but the sql statement itself, by starting with A (select from A, join B), conveys the opposite impression to me, namely that A would be the focus of the sql statement.

Similarly, the image below that only includes data from the B circle, so why is A included at all in the join statement?

Question: Working clockwise from the top right and finishing in the center, can someone provide more information about the representation of each sql image, explaining

a) why a join would be necessary in each case (for example, especially in situations where no data's taken from A or B i.e. where only A or B but not both is colored)

b) and any other detail that would clarify why the image is a good representation of the sql

解决方案

I think your main underlying confusion is that when (for example) only A is highlighted in red, you're taking that to mean "the query only returns data from A", but in fact it means "the query only returns data for those cases where A has a record". The query might still contain data from B. (For cases where B does not have a record, the query will substitute NULL.)

Similarly, the image below that only includes data from the B circle, so why is A included at all in the join statement?

If you mean — the image where A is entirely in white, and there's a red crescent-shape for the part of B that doesn't overlap with A, then: the reason that A appears in the query is, A is how it finds the records in B that need to be excluded. (If A didn't appear in the query, then Venn diagram wouldn't have A, it would only show B, and there'd be no way to distinguish the desired records from the unwanted ones.)

The image makes it seem like circle B is the primary focus of the sql statement, but the sql statement itself, by starting with A (select from A, join B), conveys the opposite impression to me, namely that A would be the focus of the sql statement.

Quite right. For this reason, RIGHT JOINs are relatively uncommon; although a query that uses a LEFT JOIN can nearly always be re-ordered to use a RIGHT JOIN instead (and vice versa), usually people will write their queries with LEFT JOIN and not with RIGHT JOIN.

这篇关于sql 连接为维恩图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！