SQL LEFT JOIN
Multiple tables can be combined into a single result set in SQL using the JOIN method, which is based on a relevant column. Among the various types of joins, the LEFT JOIN is particularly useful when you want to retrieve all records from one table while fetching matching records from another table where available.
Only the matching records from the right table are included when an LEFT JOIN is performed; all data from the left table are returned. In the event that no match is discovered, NULL will appear in the right table’s columns.
Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here
SQL LEFT JOIN Syntax
The general syntax for performing a LEFT JOIN operation in SQL is:
SELECT Table1.Column1, Table2.Column2
FROM Table1
LEFT JOIN Table2
ON Table1.CommonColumn = Table2.CommonColumn;
Let’s now take a closer look into LEFT JOIN using actual MySQL database samples.
Example 1: Employee and Department Tables
Consider two tables: staff and unit.
Staff Table
| StaffID | Staff_Name | Salary |
|---|---|---|
| 101 | Rahul Sharma | 70000 |
| 102 | Neha Verma | 52000 |
| 103 | Manish Yadav | 48000 |
| 104 | Priya Kapoor | 45000 |
| 105 | Karan Joshi | 62000 |
| 106 | Anjali Rathi | 55000 |
| 107 | Aman Taneja | 30000 |
| 108 | Simran Kaur | 58000 |
| 109 | Sahil Arora | 40000 |
| 110 | Nitin Mehta | 31000 |
Unit Table
| UnitID | Unit_Name | Staff_ID |
|---|---|---|
| 1 | HR | 101 |
| 2 | IT | 103 |
| 3 | Finance | 104 |
| 4 | Sales | 105 |
| 5 | R&D | 107 |
| 6 | Marketing | 109 |
| 7 | Support | 110 |
Query to Perform LEFT JOIN:
SELECT s.StaffID, s.Staff_Name, s.Salary, u.UnitID, u.Unit_Name
FROM staff s
LEFT JOIN unit u
ON s.StaffID = u.Staff_ID;
Output:
| StaffID | Staff_Name | Salary | UnitID | Unit_Name |
|---|---|---|---|---|
| 101 | Rahul Sharma | 70000 | 1 | HR |
| 102 | Neha Verma | 52000 | NULL | NULL |
| 103 | Manish Yadav | 48000 | 2 | IT |
| 104 | Priya Kapoor | 45000 | 3 | Finance |
| 105 | Karan Joshi | 62000 | 4 | Sales |
| 106 | Anjali Rathi | 55000 | NULL | NULL |
| 107 | Aman Taneja | 30000 | 5 | R&D |
| 108 | Simran Kaur | 58000 | NULL | NULL |
| 109 | Sahil Arora | 40000 | 6 | Marketing |
| 110 | Nitin Mehta | 31000 | 7 | Support |
Example 2: Loans and Customers
Consider two tables: credit and client.
Credit Table
| LoanID | Branch | Amount |
|---|---|---|
| 1 | B1 | 20000 |
| 2 | B2 | 15000 |
| 3 | B3 | 50000 |
| 4 | B4 | 120000 |
| 5 | B5 | 80000 |
| 6 | B6 | 35000 |
Client Table
| ClientID | Client_Name | LoanID |
|---|---|---|
| 1 | Rohan Kapoor | 1 |
| 2 | Meera Singh | 4 |
| 3 | Tanya Verma | 5 |
| 4 | Rakesh Mehta | 2 |
Query to Perform LEFT JOIN:
SELECT c.LoanID, c.Branch, c.Amount, cl.ClientID, cl.Client_Name
FROM credit c
LEFT JOIN client cl
ON c.LoanID = cl.LoanID;
Output:
| LoanID | Branch | Amount | ClientID | Client_Name |
|---|---|---|---|---|
| 1 | B1 | 20000 | 1 | Rohan Kapoor |
| 2 | B2 | 15000 | 4 | Rakesh Mehta |
| 3 | B3 | 50000 | NULL | NULL |
| 4 | B4 | 120000 | 2 | Meera Singh |
| 5 | B5 | 80000 | 3 | Tanya Verma |
| 6 | B6 | 35000 | NULL | NULL |
Example 3: Customers and Orders
Consider two tables: buyers and purchases.
Buyers Table
| BuyerID | Buyer_Name | Age | Salary |
|---|---|---|---|
| 1 | Aditya Roy | 35 | 60000 |
| 2 | Priyanka Yadav | 28 | 48000 |
| 3 | Raj Malhotra | 30 | 75000 |
| 4 | Pooja Sharma | 29 | 54000 |
Purchases Table
| PurchaseID | Purchase_Date | BuyerID | Amount |
|---|---|---|---|
| 101 | 2022-01-10 | 2 | 4500 |
| 102 | 2022-02-15 | 3 | 7000 |
Query to Perform LEFT JOIN:
SELECT b.BuyerID, b.Buyer_Name, b.Age, b.Salary, p.PurchaseID, p.Purchase_Date, p.Amount
FROM buyers b
LEFT JOIN purchases p
ON b.BuyerID = p.BuyerID;
Output:
| BuyerID | Buyer_Name | Age | Salary | PurchaseID | Purchase_Date | Amount |
|---|---|---|---|---|---|---|
| 1 | Aditya Roy | 35 | 60000 | NULL | NULL | NULL |
| 2 | Priyanka Yadav | 28 | 48000 | 101 | 2022-01-10 | 4500 |
| 3 | Raj Malhotra | 30 | 75000 | 102 | 2022-02-15 | 7000 |
| 4 | Pooja Sharma | 29 | 54000 | NULL | NULL | NULL |
Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE
By using LEFT JOIN, you can efficiently combine data from multiple tables while ensuring that all records from the left table are included, even if no corresponding data exists in the right table.
For more such SQL tutorials, keep exploring UpdateGadh!
sql left join example
sql left join multiple tables
sql join
sql left join
sql left join example
linq to sql left join
sql left join vs right join
sql left join vs left outer join
sql left join on multiple columns
sql left outer join
sql left join vs right join
right join in sql
inner join in sql
cross join in sql
