SQL Left Join

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

Customers

ID(primary key)

Customer

City

Let’s have a look at the records in the customer’s table. So that later, we can understand how

self-join is helpful:

ID	Customer	City	Items_purchased	Amount_paid
1	Peter King	Manchester	Books	120
2	Priya Krishna	New Delhi	pen	50
3	Jim Halpert	Manchester	pencil	43
4	Michael Scott	New York	Books	250
5	Harvey Spector	Birmingham	pencil	100
6	Deepa Kamat	Mumbai	Books	370
7	Anita Desai	London	pencil	50
8	Rachel Zane	Michigan	pen	70
9	Petoria John	Canberra	pen	190
10	John L	Budapest	Books	540
11	Justin Green	Ottawa City	pen	65
12	Babita Ghosh	Kolkata	pencil	75
13	Krish Pratt	London	eraser	30
14	Elizabeth Blunt	London	pencil	340
15	Nina Debrov	Amsterdam	Books	452

The schema for “cities” table is :

Number of Records: 10

Customers

ID(primary key)

city_name

country

Let’s have a look at the records in the cities table.

ID	city_name	country
1	New Delhi	India
2	Mumbai	India
3	Kolkata	India
4	London	England
5	Manchester	England
6	Ottawa City	Canada
7	Ontario	Canada
8	Pune	India
9	New York	USA
10	Washington DC	USA

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.

Code:

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.

Code:

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.

Code:

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.

Code:

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.

Code:

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.

Conclusion

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.

Recommended Articles

This is a guide to SQL Left Join. Here we discuss the examples to illustrate left joins in SQL and how does it work along with the appropriate syntax and parameters. You can also refer to our other related articles to learn more –

1. LIKE Query in SQL
2. SQL Right Join
3. Custom SQL in Tableau
4. SQL Clauses
5. Guide to MySQL Self Join