sql加入维恩图 [英] sql joins as venn diagram

问题描述

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

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.

类似地，下面的图像仅包含来自B圈的数据，那么为什么在连接语句中根本不包含A?

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

问题:从右上角开始顺时针工作并在中心结束，有人可以提供有关每个sql映像表示的更多信息，说明

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)为什么在每种情况下都需要联接(例如，特别是在没有从A或B提取数据的情况下，即仅对A或B进行着色而不对两者进行着色的情况下)

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)和其他任何可以阐明为什么图像很好地表示sql的细节

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

推荐答案

我认为您的主要内在困惑是，当(例如)仅A用红色突出显示时，您将其理解为仅查询"从A 返回数据"，但实际上这意味着对于A有记录的情况，该查询仅返回数据".查询可能仍然包含来自B的数据.(对于B没有 有记录的情况，查询将替换为NULL.)

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.)

类似地，下面的图像仅包含来自B圈的数据，那么为什么在连接语句中根本不包含A?

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

如果您的意思是— A完全为白色的图像，并且B的一部分为红色新月形，与A不重叠，则:A在查询中出现的原因是A是它在B中查找需要排除的记录的方式. (如果A没有出现在查询中，则维恩图将没有A，它将仅显示B，并且无法将所需的记录与不需要的记录区分开.)

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.)

图像使圆B看起来像是sql语句的主要焦点，但是sql语句本身以A开头(从A中选择，然后加入B)向我传达了相反的印象，即A将是sql语句的焦点.

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.

完全正确.因此，RIGHT JOIN相对不常见.尽管使用LEFT JOIN的查询几乎总是可以重新排序为使用RIGHT JOIN(反之亦然)，但通常人们会用LEFT JOIN而不是RIGHT JOIN编写查询.

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屋！