Self Join: What this query is doing

[Admin]

https://www.tutorialspoint.com/sql/sql-self-joins.htm

Clueless what this query is doing:

SQL> SELECT  a.ID, b.NAME, a.SALARY
   FROM CUSTOMERS a, CUSTOMERS b
   WHERE a.SALARY < b.SALARY;

I understand self join is creating two temporary tables out of the same table.

In the above query, two temporary tables (with aliases a and b) created out of same table CUSTOMERS. While ID and SALARY are retrieved from a, NAME from b.

Beyond that, an explanation of the above query will be helpful. Thanks in advance.

---

Reply

---

This is how the comparison happens: first row of a compared with all the rows of b, then second row of a compared with all the rows of b and so on… Cartesian product. https://www.tutorialspoint.com/sql/sql-cartesian-joins.htm

The join creating one giant table out of the table. It is comparing all the rows with all the rows, and any time b.salary is more than a.salary, it’s returning the id and salary of the lower paid individual and the name of the higher paid individual.

All the other rows where the salaries are the same or b lower get tossed from the results.

 

[Author]

Posts