Updated March 28, 2023
Introduction to SQL Left Join
A SQL LEFT join is a structured query language (SQL) statement in which when two tables are joined together, the statement returns all the rows from the left table even if all the rows do not meet the specified ON condition, however, the non-matched rows in the right table will be displayed as NULL. It is a form of the outer join.
Syntax and parameters
SELECT t1.column_name, t2.column_name FROM table_name1 as t1 LEFT JOIN table_name2 as t2 ON t1.column_name = t2.column_name WHERE condition;
The different parameters used in the syntax are :
SELECT t1.column_name, t2.column_name: It is used to select the required data from the database.
Here, t1.column_name is the column from the table’s instance t1.
t2.column_name is the column from the table’s instance t2.
FROM table_name1 LEFT JOIN table_name2: It is used to specify the source from which data has to be fetched.
Here, table_name1 is the name of the left table and table_name2 is the name of the right table. t1 and t2 are abbreviations for the tables. LEFT JOIN will fetch all records from the left table (t1) and the matched records from the right table (t2).
ON t1.column_name = t2.column_name: It is used to specify the common conditions on which the two tables will be joined. It can be a pair of primary and foreign keys.
WHERE condition: It is used to specify the conditions to filter records.
Of the above-mentioned parameters, all the parameters except the WHERE clause is mandatory. You may use GROUP BY, ORDER BY and HAVING clauses based on your requirement.
How does SQL LEFT Join work?
A SQL LEFT should be used in cases when we want all the data from one table and only matching data from the other table.
The following Venn diagram explains how SQL left join works.
Going ahead we will be discussing the above-mentioned self join in great detail.
In order to demonstrate and explain the LEFT join in SQL effectively, we will be using the following tables. These tables are made for an e-commerce website. The first table “customers ”contains customer id, names, city to which they belong. The second table “cities” contains the id, city, and country to which they belong.
The schema for the above mentioned “customers” table is :
Number of records: 15
Let’s have a look at the records in the customer’s table. So that later, we can understand how
self-join is helpful:
|2||Priya Krishna||New Delhi||pen||50|
|4||Michael Scott||New York||Books||250|
|11||Justin Green||Ottawa City||pen||65|
The schema for “cities” table is :
Number of Records: 10
Let’s have a look at the records in the cities table.
Examples of SQL Left Join
Here are a few examples to illustrate left joins in SQL.
Example #1 – SIMPLE LEFT JOIN
Find the names of customers along with the city and country to which they belong.
SELECT t1.Customer, t1.City, t2.country FROM customers as t1 LEFT JOIN cities as t2 ON t1.City = t2.city_name;
In the above example, we can notice that cities like Birmingham, Michigan, Canberra, Budapest, and Amsterdam are not present in the cities table. But since they are present in the left table(customers), they are displayed with NULL in the final results (as left join draws all records from the left table even if there is no match in the right table.)
Example #2 – LEFT JOIN with WHERE clause
Find the names of customers and their respective countries, who purchased pencils and have spent more than 50.
SELECT t1.Customer, t1.City, t2.country FROM customers as t1 LEFT JOIN cities as t2 ON t1.City = t2.city_name WHERE t1.Items_purchased= 'pencil' and t1.Amount_paid > 50;
Example #3 – LEFT JOIN WITH GROUP BY CLAUSE
Find the number of countries in which a particular category of stationery item has been purchased.
SELECT t1.Items_purchased, count(t2.country) FROM customers as t1 LEFT JOIN cities as t2 ON t1.City = t2.city_name GROUP BY t1.Items_purchased;
Example #4 – LEFT JOIN with ORDER BY clause
Find the total revenue generated by the e-commerce company across different countries ordered from highest to lowest.
SELECT t1.country, sum(t2.Amount_paid) as "Total Revenue" FROM cities as t1 LEFT JOIN customers as t2 ON t1.city_name = t2.City GROUP BY t1.country ORDER BY 2 DESC;
Example #5 – LEFT JOIN with HAVING clause
Find the countries from where customers have purchased more than one item.
SELECT t1.country, count(t2.Items_purchased ) as "No of items purchased" FROM cities as t1 LEFT JOIN customers as t2 ON t1.city_name = t2.City GROUP BY t1.country HAVING count(t2.Items_purchased ) >1 ORDER BY 2 DESC;
When performing joins in SQL, we should always try to use table aliases which are abbreviations of the given tables. This helps in writing beautiful pieces of code.
SQL left join is a statement that returns all the records from the left table even if there is no match in the right table. It is usually used when we want records from the first table only and want to check for their matches in the second table.
We hope that this EDUCBA information on “SQL Left Join” was beneficial to you. You can view EDUCBA’s recommended articles for more information.