LEFT Join in SQL

Overview: In this tutorial you will learn and understand what is LEFT join in SQL with examples.

Introduction

LEFT join in SQL returns all row records from the left side of table and only those rows from the other table where the joined fields are equal. Table name mentioned in SELECT statement after FROM keyword is always consider as Left table in LEFT join.

Syntax:

SELECT 
      column1, Column2, ....
FROM 
      left_table
        LEFT [OUTER] JOIN Right_table
             ON left_table.columnName = Right_table.columnName;

Example #1: SELECT command for LEFT join

So lets understand this with example of two table as shown below tables

left table is Customers_Tbl and right table is Orders_Tbl

We will apply the LEFT join on Customer table and below syntax will bring all common Customer ID from right table into the Left table as shown below

So you can see yellow marked rows are common in both table so only yellow marked rows will start appearing in front of Customer ID which is matched.

SELECT 
         c.Customer_ID, c.Customer_Name,  
         o.Customer_ID, o.Order_Quantity
FROM 
        Customers_Tbl c
        LEFT JOIN Orders_Tbl o
             ON c.Customer_ID = o.Customer_ID;

The new output table will look like as shown below table

So Green column is from right table that is Order_Tbl and bring all matched rows based on Customer_ID condition.

Example #2: Update Using LEFT join in SQL

How you can update your table using join in SQL. Sometime you need to bring few records from another table to merge data in one table you can say few mapping you need to do in main table using Mapping table.

So let see with example how you can update SQL table using UPDATE Command in Left Join.

Let have two table Employee and Country, you want to bring employee’s country name from another table to main employee table. So that the blank Country_name column will reflects the country name.

Below Query you can use to update Employee table

Update 
      Employee
SET Employee.Country = Country.Country_name
FROM 
     Employee
         LEFT JOIN Country
             ON Employee.Customer_ID = Country.Customer_ID 

Output of above query will be

Example #3: LEFT join SQL WHERE clause

you can also use condition to put on JOIN in SQL to bring few columns based on condition and you can use WHERE clause to make this happen,

lets take example of below tables, and we would like to bring those common Customer_ID which have more then 2000 Order_Quantity

SELECT           
       c.Customer_ID, c.Customer_Name,            
       o.Customer_ID, o.Order_Quantity 
FROM          
       Customers_Tbl c         
          LEFT JOIN Orders_Tbl o              
               ON c.Customer_ID = o.Customer_ID;
WHERE o.Order_Quantity > '2000'

Then the Output will be look like below table

So with this condition it will bring only 2 rows which have more then 2000 order quantity.