CODEWITHSUNDEEP
mysqlinner-join
Robert
Robert

Reputation: 10380

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 rows

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;

What I expected:

+-----------+-------------++-----------+-------------+                  
| 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       |
+-----------+-------------++-----------+-------------+

What I got:

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

Thanks.

Upvotes: 0

Views: 991

Answers (2)

Norbert
Norbert

Reputation: 6084

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;

How the query works:

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;

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

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 |

For example.

If the query is run with location instead of name:

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

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 is matched with Id 1 and Id 2 (using location): Resulting in 2 records for location:

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

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

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

So the end result would then be:

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

Hence your original records multiplying themselves.

Upvotes: 3

jpw
jpw

Reputation: 44871

To get the result you expected you need to join on both name and location:

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

In the question you say that you want a result matching up people with the same location and if that is the case then what you want is:

SELECT users1.name, users1.location, users2.name, users2.location
FROM users users1
JOIN users users2 ON users1.location = users2.location 
                 AND users1.name <> users2.name ;

See this sample SQL Fiddle for an example of both results

Note that the second query will duplicate the results as for each match there will be a corresponding symmetric match (Robert and Luis matches, but also Luis and Robert). I think that the following query should fix that:

SELECT users1.name, users1.location, users2.name, users2.location
FROM users users1
JOIN users users2 ON users1.location = users2.location 
AND CASE WHEN users1.name < users2.name THEN users1.name ELSE users2.name END <> users2.name;

Sample SQL Fiddle for the last query.

Upvotes: 2

Related Questions