EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials SQL Tutorial SQL Left Join

SQL Left Join

Priya Pedamkar
Article byPriya Pedamkar

Updated March 28, 2023

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

SQL Left Join

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;

Output:

SQL Left Join output 1

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;

Output:

SQL Left Join output 2

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;

Output:

output 3

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;

Output:

output 4

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;

Output:

output 5

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

We hope that this EDUCBA information on “SQL Left Join” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. LIKE Query in SQL
  2. SQL Right Join
  3. Custom SQL in Tableau
  4. SQL Clauses
  5. Guide to MySQL Self Join
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW