LEFT JOIN in SQL is a useful command when programming. Here it is Ways to use LEFT JOIN in SQL.
Join is used to retrieve records from 2 or more records based on the logical relationship between them. This relationship is determined by the join condition. SQL has two types of Join:
The Left Join function is a type of Outer Join, retrieving all records from the first table and combining them with records in the second table. First, let's find out what Outer Join is!
What is Outer Join?
Outer Join is used to join multiple database tables into a combined result set, including all records, even if they meet the Join condition. NULL values are shown based on records for which the join condition is not met.
This situation only occurs if the left table (or the first table) has more records than the right table (or the second table)…
Outer join has 3 types:
- Left Join retrieves all records from the first table.
- Right Join retrieves all records from the second table.
- Full Join retrieves records from both tables and fills inconsistent values with Null.
LEFT JOIN in SQL
LEFT JOIN in SQL the JOIN type returns all records from the left table (table 1) and matching records from the right table (table 2). If the ON clause matches no records in the right table, LEFT JOIN will still return a row in the result, but with NULL values in each column from the right table.
This means that LEFT JOIN returns all values from the left table, plus matching values from the right table, or NULL in case there are no matching values.
LEFT JOIN syntax in SQL
The basic syntax of LEFT JOIN is as follows:
SELECT cot1, cot2,... cotnFROM bang1LEFT JOIN bang2ON bang1.cot_chung = bang2.cot_chung;
Parameter:
- cot1, cot2,… cotn: names of columns to display in query results. The cot separated from each other by commas (,)
- bang1, bang2: names of tables to retrieve data when querying.
- cot_general: is usually the foreign key column name referenced from bang1 to the internal identifier column bang2 or vice versa.
Example of LEFT JOIN in SQL
Suppose the two tables are NHANVIEN and TIENTHUONG has 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 LEFT JOIN as follows:
SQL> SELECT ID, TEN, SOTIEN, NGAY
FROM NHANVIEN
LEFT JOIN TIENTHUONG
ON NHANVIEN.ID = TIENTHUONG.NHANVIEN_ID;The returned results are:
+----+----------+--------+---------------------+ | 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 | +----+----------+--------+---------------------+
LEFT JOIN with WHERE clause
The LEFT JOIN SQL command can be used with a WHERE clause. For example:
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer
WHERE Orders.amount >= 500;This is an SQL command that joins two tables and selects the row where amount is greater than or equal to 500.
Consider the example below, this query has added a WHERE clause to only return results at the “location” column position in the department table (equivalent to Bangalore). This action will filter the results to only show employees belonging to departments in Bangalore, and departments without employees will not appear in the results.
Query:
SELECT e.EmpID, e.Name, d.department_name,
d.department_head, d.location
FROM Emp e
LEFT JOIN department d ON e.department_id
= d.department_id
WHERE d.location = 'Bangalore';Result:
LEFT JOIN with AS Alias
We can use AS aliases inside LEFT JOIN to create short and neat paragraphs. For example:
SELECT C.cat_name, P.prod_title
FROM Categories1 AS C
LEFT JOIN Products AS P
ON C.cat_id= P.cat_id;This is an SQL command that selects common rows between the Category and Products tables.
LEFT JOIN VS LEFT OUTER JOIN
We can also use LEFT OUTER JOIN instead of LEFT JOIN. Basically, both these clauses are the same. That means:
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer;Equivalent to:
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.customer_id = Orders.customer;Now in this query, you will use the aliases “e” for the Emp table and “d” for the department table. Then, use the SELECT statement to reference these aliases for each column returned, making the query easier to read and enter. Alias are especially useful when you are working with tables with long or complex names because they can simplify code and make it easier to understand.
SELECT e.EmpID, e.Name, d.department_name,
d.department_head, d.location
FROM Emp e
LEFT JOIN department d ON
e.department_id = d.department_id;
Overall, LEFT JOIN SQL is an extremely useful command when programming that you must know. Hope this article helps you better understand LEFT JOIN in SQL.
See more JOIN types:
