MYSQL SELF JOIN
The SELF JOIN
is used to join the table with itself.
Syntax for SELF JOIN
SELECT column_name(s)
FROM table_name_1 T1, table_name_1 T2
WHERE condition;
Assume the table employees
with the following data.
empno | name | age | department_id | location | salary | emp_type_id |
---|---|---|---|---|---|---|
1 | Andrew | 30 | 4 | India | 100000 | 2 |
2 | Beslin | 29 | 3 | India | 90000 | 1 |
3 | Joanna | 23 | 2 | USA | 500000 | 1 |
4 | Tianna | 22 | 4 | Canada | 500000 | 1 |
5 | Ani | 31 | 4 | USA | 700000 |
Then the `employeetype' with the following data
emp_type_id | emp_type |
---|---|
1 | Full-Time |
2 | Contract |
3 | Part-Time |
Example
select A.name as CustomerName1,B.name as CustomerName2, A.location from
employees A, employees B
where A.empno <> B.empno
and A.location = B.location;
Output
CustomerName1 | CustomerName2 | location |
---|---|---|
Beslin | Andrew | India |
Andrew | Beslin | India |
Ani | Anlin | USA |
Anlin | Ani | USA |