SQL Topics
LEFT JOIN
title: LEFT JOIN
In relational databases, information is often distributed across multiple tables to reduce redundancy and improve organization.
For example:
Customers Table
Orders Table
Products Table
Departments Table
Employees TableWhile storing data in separate tables is beneficial, businesses frequently need information that exists across multiple tables.
For example:
- Which customers have placed orders?
- Which customers have never placed an order?
- Which employees are assigned to departments?
- Which employees are not assigned to any department?
An INNER JOIN only returns records that exist in both tables. However, many times businesses also need records that do not have matching values.
To solve this problem, SQL provides LEFT JOIN.
LEFT JOIN returns all records from the left table and matching records from the right table. If no match exists, SQL fills the missing values with NULL.
Because it helps identify missing relationships, LEFT JOIN is one of the most commonly used joins in real-world applications.
What is LEFT JOIN?
LEFT JOIN returns:
All Rows From Left Table
+
Matching Rows From Right TableIf no matching row exists in the right table:
NULL Values Are Returnedfor the right table columns.
Basic Example
Customers Table
| CustomerID | CustomerName |
|---|---|
| 1 | Rahul |
| 2 | Priya |
| 3 | Amit |
Orders Table
| OrderID | CustomerID |
|---|---|
| 101 | 1 |
| 102 | 2 |
Query:
SELECT *
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID =
Orders.CustomerID;Result:
| CustomerName | OrderID |
|---|---|
| Rahul | 101 |
| Priya | 102 |
| Amit | NULL |
Notice:
Amit appears even though no order exists.This is the key difference between INNER JOIN and LEFT JOIN.
Why is LEFT JOIN Important?
Businesses often need to identify:
Customers Without Orders
Employees Without Departments
Products Without Sales
Students Without CoursesINNER JOIN hides these records.
LEFT JOIN shows them.
Example:
SELECT *
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID =
Orders.CustomerID;Result includes:
Customers With Orders
Customers Without Ordersmaking analysis more complete.
Understanding the Term "Left"
In SQL:
FROM Table1
LEFT JOIN Table2Table1 is the:
Left TableTable2 is the:
Right TableLEFT JOIN guarantees:
Every Row From Left Tablewill appear in the result.
Basic LEFT JOIN Syntax
The general syntax is:
SELECT columns
FROM Table1
LEFT JOIN Table2
ON Table1.Column =
Table2.Column;Example:
SELECT Customers.CustomerName,
Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID =
Orders.CustomerID;Understanding the Syntax
Example:
SELECT Customers.CustomerName,
Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID =
Orders.CustomerID;SELECT
Specifies the columns to retrieve.
FROM
Specifies the left table.
LEFT JOIN
Specifies the right table.
ON
Defines the relationship.
CustomerID
Common column used for matching.
How LEFT JOIN Works Internally
Consider:
Customers
| CustomerID | Name |
|---|---|
| 1 | Rahul |
| 2 | Priya |
| 3 | Amit |
Orders
| OrderID | CustomerID |
|---|---|
| 101 | 1 |
| 102 | 2 |
SQL performs:
Customer 1 → Match Found
Customer 2 → Match Found
Customer 3 → No MatchSince LEFT JOIN keeps all left table rows:
Customer 3 is still returned.SQL inserts:
NULLfor missing order data.
Visual Representation
Customers:
Rahul
Priya
AmitOrders:
101
102LEFT JOIN:
Rahul → 101
Priya → 102
Amit → NULLEvery customer appears.
Creating Sample Tables
Customers:
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100)
);Orders:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT
);Insert records:
INSERT INTO Customers VALUES
(1, 'Rahul'),
(2, 'Priya'),
(3, 'Amit');INSERT INTO Orders VALUES
(101, 1),
(102, 2);First LEFT JOIN Example
Query:
SELECT
Customers.CustomerName,
Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID =
Orders.CustomerID;Result:
| CustomerName | OrderID |
|---|---|
| Rahul | 101 |
| Priya | 102 |
| Amit | NULL |
All customers appear.
Finding Customers Without Orders
One of the most important uses of LEFT JOIN.
Query:
SELECT
Customers.CustomerName
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID =
Orders.CustomerID
WHERE Orders.OrderID IS NULL;Result:
| CustomerName |
|---|
| -------------- |
| Amit |
SQL finds customers who never placed an order.
LEFT JOIN with WHERE
Example:
SELECT
Customers.CustomerName,
Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID =
Orders.CustomerID
WHERE Customers.CustomerName = 'Rahul';Result:
| CustomerName | OrderID |
|---|---|
| Rahul | 101 |
Filtering occurs after joining.
LEFT JOIN with ORDER BY
Example:
SELECT
Customers.CustomerName,
Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID =
Orders.CustomerID
ORDER BY Customers.CustomerName;Results are sorted alphabetically.
LEFT JOIN with Multiple Tables
Example:
SELECT
Customers.CustomerName,
Orders.OrderID,
Payments.Amount
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID =
Orders.CustomerID
LEFT JOIN Payments
ON Orders.OrderID =
Payments.OrderID;Three tables are connected while preserving all customer records.
Real-World Example: E-Commerce
Tables:
Customers
Orders
PaymentsManagement wants:
All Customers
Even Those Who Never Purchased AnythingLEFT JOIN makes this possible.
Real-World Example: Human Resources
Tables:
Employees
DepartmentsQuery:
SELECT
Employees.EmployeeName,
Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID =
Departments.DepartmentID;Employees without departments still appear.
Real-World Example: Education System
Tables:
Students
EnrollmentsLEFT JOIN identifies:
Students Who Have Not Enrolledin any course.
LEFT JOIN vs INNER JOIN
Consider:
Customers
| CustomerID | Name |
|---|---|
| 1 | Rahul |
| 2 | Priya |
| 3 | Amit |
Orders
| OrderID | CustomerID |
|---|---|
| 101 | 1 |
| 102 | 2 |
INNER JOIN Result:
Rahul
PriyaOnly matching rows.
LEFT JOIN Result:
Rahul
Priya
AmitAll left table rows.
Comparison:
| Feature | INNER JOIN | LEFT JOIN |
|---|---|---|
| Matching Rows | Yes | Yes |
| Unmatched Left Rows | No | Yes |
| NULL Values Possible | No | Yes |
| Complete Left Table Data | No | Yes |
Working with NULL Values
When no match exists:
Right Table Columns
=
NULLExample:
| CustomerName | OrderID |
|---|---|
| Amit | NULL |
Always remember:
NULL Means No Matching Record FoundPerformance Considerations
LEFT JOIN is generally efficient.
Performance depends on:
- Indexes
- Table Size
- Join Conditions
- Database Engine
Joining large tables without indexes may slow down queries.
Common Errors
Using Wrong Join Column
Wrong:
ON Customers.Name =
Orders.OrderIDAlways use related columns.
Confusing LEFT JOIN and INNER JOIN
LEFT JOIN returns unmatched left records.
INNER JOIN does not.
Ignoring NULL Values
Many developers forget:
NULL indicates no match.Filtering Incorrectly
Wrong:
WHERE Orders.OrderID > 0May unintentionally remove unmatched rows.
Best Practices
Join Using Keys
Prefer:
Primary Key ↔ Foreign Keyrelationships.
Use Table Aliases
Example:
Customers C
Orders OImproves readability.
Select Only Required Columns
Avoid unnecessary data retrieval.
Understand NULL Results
Always handle NULL values properly.
Index Join Columns
Improves performance significantly.
Common Interview Questions
What is LEFT JOIN?
LEFT JOIN returns all rows from the left table and matching rows from the right table.
What happens when no match exists?
SQL returns NULL values for right-table columns.
Which table is always fully returned?
The left table.
How can LEFT JOIN find missing records?
By filtering:
WHERE RightTable.Column IS NULLWhat is the main difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only matches.
LEFT JOIN returns all left-table records.
Summary
LEFT JOIN is one of the most important SQL joins because it allows developers to retrieve all records from a primary table while also including related data when available. It is widely used to identify missing relationships and generate complete business reports.
In this lesson, you learned:
- What LEFT JOIN is
- Why LEFT JOIN is important
- LEFT JOIN syntax
- Internal working
- Finding unmatched records
- LEFT JOIN with WHERE
- LEFT JOIN with ORDER BY
- LEFT JOIN with multiple tables
- NULL handling
- Real-world examples
- Performance considerations
- Best practices
Mastering LEFT JOIN is essential because many real-world reporting and analytical queries require visibility into both matching and non-matching records.
Next Step
Continue to the next lesson:
RIGHT JOIN →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for LEFT JOIN.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, joins, left
Related SQL Topics