FULL JOIN in SQL

FULL JOIN in SQL returns all records in the left table and the table must be combined. In other words, this clause is a combination of both LEFT and RIGHT JOIN results.

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 in SQL Picture 1

1. JOIN clause in SQL

Syntax FULL JOIN in SQL

FULL JOIN basic syntax is as follows:

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

Parameters :

1. cot1, cot2, . cotn : the names of the columns to display in the query results. Cots are separated by commas (,)
2. bang1, bang2 : table names to retrieve data when querying.
3. cot_chung: usually the foreign key column name referring to bang1 to the identifier column in bang2 or vice versa.

FULL JOIN example in SQL

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 

See more types of JOIN:

1. INNER JOIN - Returns records with matching values ​​between two tables.
2. LEFT JOIN - Returns all records from the left panel and matching records from the right panel.
3. RIGHT JOIN - Returns all records from the right panel and the appropriate records from the left panel.
4. SELF JOIN - Combine a table with itself as if the table is considered two tables, change the temporary name at least one table in the SQL command.
5. CARTESIAN JOIN - Returns the Cartesian product of record sets from two or more combined tables.