为什么在使用内部联接时得到不同的结果 [英] Why I get different result when using inner join

问题描述

我有一个包含以下各列的表； a，b，e和另一个包含c，d的表table2.两个表都有一列name.

I have a table that contains the following columns; a, b, e and another table, table2 that contains c,d. Both tables have a column name.

我进行了查询:

SELECT distinct a, b
from db.table
where e <>'65';

我说是1885年记录

然后，我需要另一个表(c，d)的更多信息.我使用内部联接来获取这些附加信息.查询变为:

Then, I needed more information from another table (c, d). I used inner join to get these additional information. The query becomes:

SELECT distinct a, b, c, d 
from db.table
inner join db.table2
on table.name=table2.name2
where e <>'65';

现在，我期望的结果是相同的，但增加了列(c，d).但是我得到了不同的结果:51144.任何人都可以解释一下为什么我只是增加了更多列却没有改变条件而导致数目增加了吗?

Now, I was expecting the same result but with additional columns (c, d). But I got a different number of results: 51144. Can any body explain please why the number increased while I just added more columns and did not change the condition ?

推荐答案

DISTINCT就像关键字中所说的那样，从选择中返回不同的结果.

DISTINCT returns as the key word says, the distinct results from the select.

向其他选择添加其他列的事实可能会导致不同结果集更多行.让我们看一个例子

The fact that you are adding additional columns to the distinct select will possibly cause the distinct result set to be more rows. Lets have a look at an example

与

A   B
1   2
1   3
1   2

将是

A   B
1   2
1   3

但是现在添加更多的列

A   B   C
1   2   1
1   3   2
1   2   3

将导致

A   B   C
1   2   1
1   3   2
1   2   3

此外，内部联接可以限制结果集，因为内部联接将仅返回table1和table2中都存在的值，因此它在表1中存在给定值，而在表2中不存在，因此将不返回该值.

Further more, your inner join can limit the result set, as inner join will only return values that are in both table1 and table2, so it a given value is present in table1, but not in table2 it will not be returned.

或者如@zerkms所述，如果有2个键定义两个表之间的关系，则可能会得到更多的期望值.

Or as was mentioned by @zerkms, if there were 2 keys defining the relationship between the 2 tables, you might get more that you expected.

这篇关于为什么在使用内部联接时得到不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！