FULL JOIN in SQL

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

The combined table will contain all records from both tables and fill in that the NULL value for the values does not match.

FULL JOIN basic syntax is as follows:

SELECT cot1, cot2,. cotn
FROM bang1
FULL JOIN bang2
ON bang1.cot_chung = bang2.cot_chung;

Parameters :

• Cot1, cot2, . Cotn : the names of the columns to display in the query results Cots Are separated by commas (, )
• Bang1, bang2 : table names to retrieve data when querying.
• Cot_chung: Usually the foreign key column name Referring to Bang1 To the identifier column in Bang2 Or vice versa.

Suppose the two tables are NHANVIEN And TIENTHUONG With 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 | +----+----------+-----+-----------+----------+

Table 2: TIENTHUONG

 +-----+---------------------+-------------+--------+ |TT_ID| NGAY | NHANVIEN_ID | SOTIEN | +-----+---------------------+-------------+--------+ | 102 | 2019-01-08 00:00:00 | 3 | 3000 | | 100 | 2019-01-08 00:00:00 | 3 | 1500 | | 101 | 2019-02-20 00:00:00 | 2 | 1560 | | 103 | 2018-12-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+ 

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

SQL> SELECT ID, TEN, SOTIEN, NGAY 
 FROM NHANVIEN 
 FULL JOIN TIENTHUONG 
 ON NHANVIEN.ID = TIENTHUONG.NHANVIEN_ID;

The result is:

 +------+----------+--------+---------------------+ | ID | TEN | SOTIEN | NGAY | +------+----------+--------+---------------------+ | 1 | Thanh | NULL | NULL | | 2 | Loan | 1560 | 2019-02-20 00:00:00 | | 3 | Nga | 3000 | 2019-01-08 00:00:00 | | 3 | Nga | 1500 | 2019-01-08 00:00:00 | | 4 | Manh | 2060 | 2018-12-20 00:00:00 | | 5 | Huy | NULL | NULL | | 6 | Cao | NULL | NULL | | 7 | Lam | NULL | NULL | | 3 | Nga | 3000 | 2019-01-08 00:00:00 | | 3 | Nga | 1500 | 2019-01-08 00:00:00 | | 2 | Loan | 1560 | 2019-02-20 00:00:00 | | 4 | Manh | 2060 | 2018-12-20 00:00:00 | +------+----------+--------+---------------------+ 

If your Database does not support FULL JOIN (such as MySQL) then you can use the UNION ALL statement to combine two JOINs as follows:

SQL> SELECT ID, TEN, SOTIEN, NGAY
 FROM NHANVIEN
 LEFT JOIN TIENTHUONG
 ON NHANVIEN.ID = TIENTHUONG.NHANVIEN_ID
UNION ALL
 SELECT ID, TEN, SOTIEN, NGAY
 FROM NHANVIEN
 RIGHT JOIN TIENTHUONG
 ON NHANVIEN.ID = TIENTHUONG.NHANVIEN_ID