Search code examples
mysqljoinself-join

Understanding SQL self-join

I am trying to understand SQL Self-joins. I have the following table 'persons' which has firstname, lastname, address and city of the employee (source W3Schools): 

mysql> select * from persons;
+------+-----------+-----------+--------------+-----------+
| P_id | LastName  | FirstName | Address      | City      |
+------+-----------+-----------+--------------+-----------+
|    1 | Hansen    | Ola       | Timoteivn 10 | Sandnes   | 
|    2 | Svendson  | Tove      | Borgvn 23    | Sandnes   |
|    3 | Pettersen | Kari      | Storgt 20    | Stavanger |
+------+-----------+-----------+--------------+-----------+

I now want to return the names of the employees which have same city as that of 'Hansen Ola'. So i write a self-join and it works fine: 

mysql> select p1.Lastname, p1.firstname from persons p1, persons p2 where p1.city =    p2.city and p2.lastname = 'Hansen';
+----------+-----------+
| Lastname | firstname |
+----------+-----------+
| Hansen   | Ola       |
| Svendson | Tove      |
+----------+-----------+

However, if i change the alias p2 to p1 i.e. p2.lastname = 'Hansen' to p1.lastname = 'Hansen', then i don't get names of both the employees. 

mysql> select p1.Lastname, p1.firstname from persons p1, persons p2 where p1.city = p2.city and p1.lastname = 'Hansen';
+----------+-----------+
| Lastname | firstname |
+----------+-----------+
| Hansen   | Ola       |
| Hansen   | Ola       |
+----------+-----------+

Could somebody please help me understand why changing alias from p2 to p1 changes the result? Thank you.

Solution

  • Let me highlight your query and hopefully it will make more sense:

    select p1.lastname, ...
from persons p1, persons p2
where ... and p1.lastname = 'Hansen'

    So you are limiting the results to only return records from the first table, p1. And simultaneously you are restricting the first table to only be those with lastname = 'Hansen'. If you picture the unrestricted result set, it may make more sense (I removed the abiguous portion of the WHERE clause to show the full product):

    select p1.Lastname as p1_lastname, p2.lastname as p2_lastname
from persons p1, persons p2
where p1.city = p2.city

P1_LASTNAME     P2_LASTNAME
Hansen  Hansen
Svendson    Hansen
Hansen  Svendson
Svendson    Svendson
Pettersen   Pettersen

    If you take the above resultset and add the condition p1.lastname = "Hansen", it's no surprise you only get 'Hansen' out of it.