SELF JOIN in SQL

This guide covers self join in SQL with practical context and easy-to-follow details. Use it to understand the subject and apply the information confidently.

SELF JOIN In SQL is used to execute Join on the same table by concatenating a table with itself, considering it to be two tables, and replacing at least one temporary table name.

• JOIN clause in SQL

The basic syntax of the SELF JOIN is as follows:

 SELECT a.ten_cot, b.ten_cot . 
 FROM bang1 a, bang1 b 
 WHERE a.cot_chung = b.cot_chung; 

Here, the WHERE clause can be any expression at your request.

Suppose the NHANVIEN Table has the following records:

Table 1: NHANVIEN

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+ 

Now, let's join these two tables using the SELF JOIN as follows:

SQL> SELECT a.ID, b.TEN, a.LUONG 
 FROM NHANVIEN a, NHANVIEN b 
 WHERE a.LUONG 

The result is:

 +----+----------+---------+ | ID | TEN | LUONG | +----+----------+---------+ | 2 | Thanh | 1500.00 | | 2 | Nga | 1500.00 | | 1 | Manh | 2000.00 | | 2 | Manh | 1500.00 | | 3 | Manh | 2000.00 | | 6 | Manh | 4500.00 | | 1 | Huy | 2000.00 | | 2 | Huy | 1500.00 | | 3 | Huy | 2000.00 | | 4 | Huy | 6500.00 | | 6 | Huy | 4500.00 | | 1 | Cao | 2000.00 | | 2 | Cao | 1500.00 | | 3 | Cao | 2000.00 | | 1 | Lam | 2000.00 | | 2 | Lam | 1500.00 | | 3 | Lam | 2000.00 | | 4 | Lam | 6500.00 | | 5 | Lam | 8500.00 | | 6 | Lam | 4500.00 | +----+----------+---------+