LEFT JOIN in SQL

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

This means that the LEFT JOIN returns all values from the left table, plus the appropriate values from the right table or NULL in the case of no matching values.

The basic LEFT JOIN syntax is as follows:

SELECT cot1, cot2,. cotn
FROM bang1
LEFT 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 the LEFT JOIN as follows:

SQL> SELECT ID, TEN, SOTIEN, NGAY 
 FROM NHANVIEN 
 LEFT 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 | +----+----------+--------+---------------------+