LEFT JOIN in SQL

The LEFT JOIN in SQL is the type of JOIN that returns all records from the left table (table 1) and the appropriate records from the right table (table 2). If the ON clause does not match any record in the right table, LEFT JOIN will still return a row in the result, but the value is NULL in each column from the right table.

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.

1. JOIN clause in SQL

Syntax LEFT JOIN in SQL

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 :

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.

Example of LEFT JOIN 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 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 | +----+----------+--------+---------------------+ 

See more types of JOIN:

1. INNER JOIN - Returns records with matching values ​​between two tables.
2. RIGHT JOIN - Returns all records from the right panel and the appropriate records from the left panel.
3. FULL JOIN - Returns all records in the left panel and the table must be combined.
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.