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.
- 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 :
- 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.
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:
- INNER JOIN - Returns records with matching values between two tables.
- 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.
- 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.
- CARTESIAN JOIN - Returns the Cartesian product of record sets from two or more combined tables.
You should read it
May be interested
- How to fix the problem of left clicking on Windowsusers often encounter problems such as not being able to click the left mouse button or the left mouse button in the wrong way. there are many reasons for this problem, so the article will show you some ways to troubleshoot left click on windows.
- The LEFT function, how to use the left-hand string cutting function in Excelthe left function in excel is used to trim the left string of characters in data parameters.
- Why are some people left-handed while most of us are right-handed?did you know, 1/10 of the world population is left-handed and this has existed 500,000 years ago. and it is a fact that we cannot choose the right hand, because this is decided since the human being is not born, still in the womb.
- Why are you and many other countries in the world driving on the left?you are surprised to know that up to 76 countries and territories around the world still apply the ride rules on the left side road. why? what's the difference between driving on the right and on the left and which side driver is there first?
- LEFT function in Excel, how to use LEFT function and illustrative examplesleft function in excel, how to use left function and illustrative examples. left function in excel is a function of string processing function, you use left function when you need to cut the character string to the left of the text string. if you need to learn more about mid functions, be patient
- Set up a computer mouse for left-handed peoplein fact there are many left-handed people but the rate is very small compared to the number of right-handed people. therefore, they are forced to change their habits, using their hands not to agree on activities or objects designed for the majority. but for those who have difficulty adapting, it is difficult enough to hold and move a computer mouse.
- How to turn off Pick Up Where You Left Off in Microsoft Officemicrosoft office has a lot of features enabled by default, such as pick up where you left off. some users find this feature useful while others do not. here's how to disable this feature.
- Do you know the structure and usage of the LEFT function in Excel?this article guides you in detail about the structure and usage of common and effective left function in excel
- This is why you should sleep on your left side?just leaning on your left side, you can significantly improve your health!
- As you know, why is the leaning leg of the motorcycle on the left?even though we ride a motorbike every day, most of us do not understand the car we still use, specifically why the vehicle's inclined leg is on the left side? let's find the answer to this question!