CODEWITHSUNDEEP
mysqlself-join
Robert
Robert

Reputation: 10380

MySql self join predicate not understanding how it works

I am learning about self joins and have this simple 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       |
+-----------+-------------+

What was intended was to find all the people who share the same location as 'Robert'.

Using self joins I saw that the following works from reading about solutions on here but, I don't understand what is going on and would not be able to explain it to another if they asked me how it worked:

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

Correctly returns the following result:

+-----------+
| name      |
+-----------+
| Robert    |
| Manuel    |
| Dalia     |
| Alejandra |
| Luis      |
| Monica    |
| Claudia   |
| Scartlet  |
| Sergio    |
+-----------+

What I don't understand specifically is how the AND users2.name = 'Robert' plays a role in returning the correct result.

Can someone explain step by step how MySql processes the table expression and predicate: 

WHERE users1.location = users2.location
AND users2.name = 'Robert';

to return the correct result.

Upvotes: 2

Views: 81

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

I actually prefer to use explicit syntax in a self-join which makes it easier to see that you are actually joining together two tables (or a table to itself in this case):

SELECT u2.name
FROM users u1 INNER JOIN users u2
ON u1.location = u2.location
WHERE u1.name = 'Robert'

A picture is worth a thousand words, so here is what the temporary table created in the above query looks like:

+-----------+-------------+-----------+-------------+
| u1.name   | u1.location | u2.name   | u2.location |
+-----------+-------------+-----------+-------------+
| Robert    | Guadalajara | 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 | Scarlet   | Guadalajara |
| Robert    | Guadalajara | Sergio    | Guadalajara |
+-----------+-------------+-----------+-------------+

The query joins each location on the first users table to each matching location in the second users table. The WHERE clause restricts to only users named 'Robert' in the first table.

Upvotes: 1

Related Questions