A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table.
Syntax :
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;
For understanding self join, assume below customer table.
Customer Table :
Now, we have to find all pairs of customers having the same rating without duplication. So, Actually this query wants two things,First same rating and second is without duplication.
Let us, understand this step by step,
we run below query first to find same rating,
SQL> SELECT C1.CNAME,C2.CNAME,C1.RATING FROM CUSTOMER C1,CUSTOMER C2 WHERE C1.RATING=C2.RATING ORDER BY C1.RATING;
Above query gives below result...
Here question is our main query want same rating as well as no duplication but in above result there are so many duplicated rows like,
HARDIK HARDIK 100
GITA GITA 100
and
GITA LAXIT 200
LAXIT GITA 200 is also duplicate.
Note: So removing duplication, we use another condition along with C1.RATING=C2.RATING in where clause.
Final query becomes like,
SQL> SELECT C1.CNAME,C2.CNAME,C1.RATING FROM CUSTOMER C1,CUSTOMER C2
WHERE C1.CNUM>C2.CNUM AND C1.RATING=C2.RATING
ORDER BY C1.RATING;
Above query gives below result which we want,
