自连接返回的行比预期的多 [英] Self join returning more rows than expected

问题描述

我是自我联接的新手，并组成了以下示例表:

I am new to self joins and made up the following example table:

+-----------+-------------+
| name      | location    |
+-----------+-------------+
| Robert    | Guadalajara |
| Manuel    | Guadalajara |
| Dalia     | Guadalajara |
| Alejandra | Guadalajara |
| Luis      | Guadalajara |
| Monica    | Guadalajara |
| Claudia   | Guadalajara |
| Scartlet  | Guadalajara |
| Sergio    | Guadalajara |
| Rick      | Mexico City |
| Rene      | Mexico City |
| Ramon     | Culiacan    |
| Junior    | Culiacan    |
| Kasandra  | Culiacan    |
| Emma      | Culiacan    |
| Johnatha  | Dunedin     |
| Miriam    | Largo       |
| Julie     | Largo       |
+-----------+-------------+

18行

我想运行一个简单的查询，通过以下查询匹配具有相同位置的人:

I wanted to run a simple query matching up people with the same location with the following query:

SELECT users1.name, users1.location, users2.name, users2.location
FROM users users1, users users2
WHERE users1.location = users2.location;

我所期望的:

+-----------+-------------++-----------+-------------+                  
| name      | location    || name      | location    |
+-----------+-------------++-----------+-------------+
| Robert    | Guadalajara || Robert    | Guadalajara |
| Manuel    | Guadalajara || Manuel    | Guadalajara |
| Dalia     | Guadalajara || Dalia     | Guadalajara |
| Alejandra | Guadalajara || Alejandra | Guadalajara |
| Luis      | Guadalajara || Luis      | Guadalajara |
| Monica    | Guadalajara || Monica    | Guadalajara |
| Claudia   | Guadalajara || Claudia   | Guadalajara |
| Scartlet  | Guadalajara || Scartlet  | Guadalajara |
| Sergio    | Guadalajara || Sergio    | Guadalajara |
| Rick      | Mexico City || Rick      | Mexico City |
| Rene      | Mexico City || Rene      | Mexico City |
| Ramon     | Culiacan    || Ramon     | Culiacan    |
| Junior    | Culiacan    || Junior    | Culiacan    |
| Kasandra  | Culiacan    || Kasandra  | Culiacan    |
| Emma      | Culiacan    || Emma      | Culiacan    |
| Johnatha  | Dunedin     || Johnatha  | Dunedin     |
| Miriam    | Largo       || Miriam    | Largo       |
| Julie     | Largo       || Julie     | Largo       |
+-----------+-------------++-----------+-------------+

我得到了什么:

+-----------+-------------+-----------+-------------+
| name      | location    | name      | location    |
+-----------+-------------+-----------+-------------+
| Robert    | Guadalajara | Robert    | Guadalajara |
| Manuel    | Guadalajara | Robert    | Guadalajara |
| Dalia     | Guadalajara | Robert    | Guadalajara |
| Alejandra | Guadalajara | Robert    | Guadalajara |
| Luis      | Guadalajara | Robert    | Guadalajara |
| Monica    | Guadalajara | Robert    | Guadalajara |
| Claudia   | Guadalajara | Robert    | Guadalajara |
| Scartlet  | Guadalajara | Robert    | Guadalajara |
| Sergio    | Guadalajara | Robert    | Guadalajara |
| Robert    | Guadalajara | Manuel    | Guadalajara |
| Manuel    | Guadalajara | Manuel    | Guadalajara |
| Dalia     | Guadalajara | Manuel    | Guadalajara |
| Alejandra | Guadalajara | Manuel    | Guadalajara |
| Luis      | Guadalajara | Manuel    | Guadalajara |
| Monica    | Guadalajara | Manuel    | Guadalajara |
| Claudia   | Guadalajara | Manuel    | Guadalajara |
| Scartlet  | Guadalajara | Manuel    | Guadalajara |
| Sergio    | Guadalajara | Manuel    | Guadalajara |
| Robert    | Guadalajara | Dalia     | Guadalajara |
| Manuel    | Guadalajara | Dalia     | Guadalajara |
| Dalia     | Guadalajara | Dalia     | Guadalajara |
| Alejandra | Guadalajara | Dalia     | Guadalajara |
| Luis      | Guadalajara | Dalia     | Guadalajara |
| Monica    | Guadalajara | Dalia     | Guadalajara |
| Claudia   | Guadalajara | Dalia     | Guadalajara |
| Scartlet  | Guadalajara | Dalia     | Guadalajara |
| Sergio    | Guadalajara | Dalia     | Guadalajara |
| Robert    | Guadalajara | Alejandra | Guadalajara |
| Manuel    | Guadalajara | Alejandra | Guadalajara |
| Dalia     | Guadalajara | Alejandra | Guadalajara |
| Alejandra | Guadalajara | Alejandra | Guadalajara |
| Luis      | Guadalajara | Alejandra | Guadalajara |
| Monica    | Guadalajara | Alejandra | Guadalajara |
| Claudia   | Guadalajara | Alejandra | Guadalajara |
| Scartlet  | Guadalajara | Alejandra | Guadalajara |
| Sergio    | Guadalajara | Alejandra | Guadalajara |
| Robert    | Guadalajara | Luis      | Guadalajara |
| Manuel    | Guadalajara | Luis      | Guadalajara |
| Dalia     | Guadalajara | Luis      | Guadalajara |
| Alejandra | Guadalajara | Luis      | Guadalajara |
| Luis      | Guadalajara | Luis      | Guadalajara |
| Monica    | Guadalajara | Luis      | Guadalajara |
| Claudia   | Guadalajara | Luis      | Guadalajara |
| Scartlet  | Guadalajara | Luis      | Guadalajara |
| Sergio    | Guadalajara | Luis      | Guadalajara |
| Robert    | Guadalajara | Monica    | Guadalajara |
| Manuel    | Guadalajara | Monica    | Guadalajara |
| Dalia     | Guadalajara | Monica    | Guadalajara |
| Alejandra | Guadalajara | Monica    | Guadalajara |
| Luis      | Guadalajara | Monica    | Guadalajara |
| Monica    | Guadalajara | Monica    | Guadalajara |
| Claudia   | Guadalajara | Monica    | Guadalajara |
| Scartlet  | Guadalajara | Monica    | Guadalajara |
| Sergio    | Guadalajara | Monica    | Guadalajara |
| Robert    | Guadalajara | Claudia   | Guadalajara |
| Manuel    | Guadalajara | Claudia   | Guadalajara |
| Dalia     | Guadalajara | Claudia   | Guadalajara |
| Alejandra | Guadalajara | Claudia   | Guadalajara |
| Luis      | Guadalajara | Claudia   | Guadalajara |
| Monica    | Guadalajara | Claudia   | Guadalajara |
| Claudia   | Guadalajara | Claudia   | Guadalajara |
| Scartlet  | Guadalajara | Claudia   | Guadalajara |
| Sergio    | Guadalajara | Claudia   | Guadalajara |
| Robert    | Guadalajara | Scartlet  | Guadalajara |
| Manuel    | Guadalajara | Scartlet  | Guadalajara |
| Dalia     | Guadalajara | Scartlet  | Guadalajara |
| Alejandra | Guadalajara | Scartlet  | Guadalajara |
| Luis      | Guadalajara | Scartlet  | Guadalajara |
| Monica    | Guadalajara | Scartlet  | Guadalajara |
| Claudia   | Guadalajara | Scartlet  | Guadalajara |
| Scartlet  | Guadalajara | Scartlet  | Guadalajara |
| Sergio    | Guadalajara | Scartlet  | Guadalajara |
| Robert    | Guadalajara | Sergio    | Guadalajara |
| Manuel    | Guadalajara | Sergio    | Guadalajara |
| Dalia     | Guadalajara | Sergio    | Guadalajara |
| Alejandra | Guadalajara | Sergio    | Guadalajara |
| Luis      | Guadalajara | Sergio    | Guadalajara |
| Monica    | Guadalajara | Sergio    | Guadalajara |
| Claudia   | Guadalajara | Sergio    | Guadalajara |
| Scartlet  | Guadalajara | Sergio    | Guadalajara |
| Sergio    | Guadalajara | Sergio    | Guadalajara |
| Rick      | Mexico City | Rick      | Mexico City |
| Rene      | Mexico City | Rick      | Mexico City |
| Rick      | Mexico City | Rene      | Mexico City |
| Rene      | Mexico City | Rene      | Mexico City |
| Ramon     | Culiacan    | Ramon     | Culiacan    |
| Junior    | Culiacan    | Ramon     | Culiacan    |
| Kasandra  | Culiacan    | Ramon     | Culiacan    |
| Emma      | Culiacan    | Ramon     | Culiacan    |
| Ramon     | Culiacan    | Junior    | Culiacan    |
| Junior    | Culiacan    | Junior    | Culiacan    |
| Kasandra  | Culiacan    | Junior    | Culiacan    |
| Emma      | Culiacan    | Junior    | Culiacan    |
| Ramon     | Culiacan    | Kasandra  | Culiacan    |
| Junior    | Culiacan    | Kasandra  | Culiacan    |
| Kasandra  | Culiacan    | Kasandra  | Culiacan    |
| Emma      | Culiacan    | Kasandra  | Culiacan    |
| Ramon     | Culiacan    | Emma      | Culiacan    |
| Junior    | Culiacan    | Emma      | Culiacan    |
| Kasandra  | Culiacan    | Emma      | Culiacan    |
| Emma      | Culiacan    | Emma      | Culiacan    |
| Johnatha  | Dunedin     | Johnatha  | Dunedin     |
| Miriam    | Largo       | Miriam    | Largo       |
| Julie     | Largo       | Miriam    | Largo       |
| Miriam    | Largo       | Julie     | Largo       |
| Julie     | Largo       | Julie     | Largo       |
+-----------+-------------+-----------+-------------+

有人可以向我解释为什么除了建议的解决方案外还能得到这个结果吗?我想知道结果如何以及为什么产生.

Can someone explain to me why I got this result aside from a suggested solution? I would like to know how and why this resulted.

谢谢.

推荐答案

您在位置上进行了匹配，该匹配发生了多次.将结果乘以当前的结果.

You matched on location, which occurs multiple times. This is multiplied out with as result your current result.

您的预期结果是加入名称:

Your expected result was a join on name:

SELECT users1.name, users1.location, users2.name, users2.location
FROM users users1, users users2
WHERE users1.name = users2.name;

查询的工作方式:

在数据集中，WHERE中的列是匹配的.这等效于一个JOIN:

In a dataset the columns in the WHERE are matched. This is equivalent to a JOIN:

SELECT users1.name, users1.location, users2.name, users2.location
FROM users users1
INNER JOIN users users2 ON users1.name = users2.name;

哪个是当今更常用的语法.这两个查询是相同的，但是使用JOIN，它变得更具可读性和灵活性.

Which is the nowadays more often used syntax. Both queries are the same, however with the JOIN it becomes more readable and flexible.

JOIN中的匹配是在 users1.name 上等于 users2.name 进行的.然后显示找到的结果:

The match in the JOIN is made on the users1.name being equal to the users2.name. The found results are then displayed:

|罗伯特|瓜达拉哈拉|

| Robert | Guadalajara |

例如.

如果查询运行时使用的是位置而不是名称:

If the query is run with location instead of name:

INNER users users2 ON users1.location = users2.location;

SQL匹配位置.因此，对于一个简短易读的示例，请想象一下这个简短的表格.添加列ID以显示收集结果的方式:

SQL matches the locations. So for a shorter more readable example imagine this short table. The column ID is added to show the way the results are collected:

+-----------+-------------+----+
| name      | location    | ID |
+-----------+-------------+----+
| Robert    | Guadalajara | 1  |
| Manuel    | Guadalajara | 2  |

这与位置匹配，发生两次:

This is matched on location, which occurs twice:

Id 1与Id 1和Id 2匹配(使用位置):产生2条位置记录:

Id 1 is matched with Id 1 and Id 2 (using location): Resulting in 2 records for location:

 | Robert    | Guadalajara | 1  |
 | Manuel    | Guadalajara | 1  |

Id 2与Id 1和Id 2匹配(使用位置):产生2条位置记录:

Id 2 is matched with Id 1 and Id 2 (using location): Resulting in 2 records for location:

 | Robert    | Guadalajara | 2  |
 | Manuel    | Guadalajara | 2  |

因此最终结果将是:

| Robert    | Guadalajara | 1  |
| Manuel    | Guadalajara | 1  |
| Robert    | Guadalajara | 2  |
| Manuel    | Guadalajara | 2  |

因此，原始记录会成倍增加.

Hence your original records multiplying themselves.

这篇关于自连接返回的行比预期的多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！