SQL Self Join

SQL Self Join is used to join a table with itself, calling it Unary relationships. Each row of the table is associated with itself and every other row in the table.



Self Join Syntax :

The syntax of the Self Join is −

SELECT column-name(s)
FROM table1 T1, table1 T2
WHERE condition;



SQL Self Join Example :

Consider the Customer table with the following records -

ID Name Age Address Salary
1 Ramesh 36 Udaipur 20000
2 Khilan 33 Mumbai 15000
3 kaushik 28 Chennai 20000
4 Chaitali 29 Udaipur 65000
5 Hardik 27 Mumbai 85000
6 Komal 33 Chennai 45000
7 Muffy 23 Delhi 100000


TEST CASE 1 :-Let us join these two tables using the Self JOIN as follows

  SELECT  a.ID, b.Name, a.Salary
   FROM Customer a, Customer b
   WHERE a.Salary < b.Salary;


The result for the respective sql query is as follows −

ID Name Amount
2 Ramesh 15000
2 kaushik 15000
1 Chaitali 20000
2 Chaitali 15000
3 Chaitali 20000
6 Chaitali 45000
1 Hardik 20000
2 Hardik 15000
3 Hardik 20000
4 Hardik 65000
6 Hardik 45000
1 Komal 20000
2 Komal 15000
3 Komal 20000
1 Muffy 20000
2 Muffy 15000
3 Muffy 20000
4 Muffy 65000
5 Muffy 85000
6 Muffy 45000




Visit :


Discussion


* You must be logged in to add comment.