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

Most Read