SELF JOIN in SQL

Each Join operation must have two tables, but there are cases where you have to perform JOIN on the same table, but you can call this JOIN operation SELF JOIN.

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.

1. JOIN clause in SQL

Syntax SELF JOIN 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.

Example of SELF JOIN in SQL

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 | +----+----------+---------+ 
See more types of JOIN:

INNER JOIN - Returns records with matching values ​​between two tables.
LEFT JOIN - Returns all records from the left panel and matching records from the right panel.
RIGHT JOIN - Returns all records from the right panel and the appropriate records from the left panel.
FULL JOIN - Returns all records in the left panel and the table must be combined.
CARTESIAN JOIN - Returns the Cartesian product of record sets from two or more combined tables.