In this article we going to learn SQL joins — INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN — with real examples so you understand exactly when to use each one and why.
If you've been writing SQL for a while, you've definitely used joins. But a lot of developers just stick to INNER JOIN for everything because it works most of the time, and then get confused when results are missing or have NULL values. Understanding all four join types properly saves you a lot of debugging time and makes your queries much more accurate.
Joins are how you combine data from multiple tables. A customer is in one table, their orders are in another, the products inside those orders are in a third table. Joins stitch them together into one result. Without joins, relational databases wouldn't make much sense.
Let me walk through each join type with the same set of sample tables so you can directly compare the results.
This tutorial shows how to:
- Understand what SQL joins are and why we need them
- Use INNER JOIN to get matching rows from both tables
- Use LEFT JOIN to keep all rows from the left table
- Use RIGHT JOIN to keep all rows from the right table
- Use FULL JOIN to keep all rows from both tables
- Join more than two tables together
- Use joins with WHERE, GROUP BY, and aggregate functions
- Avoid common mistakes like duplicate rows and missing data
The Sample Tables We'll Use
Throughout this article we'll use two simple tables — Customers and Orders. Let's create them and insert some data so you can follow along and run these queries yourself.
CREATE TABLE Customers (
CustomerId INT PRIMARY KEY,
CustomerName NVARCHAR(100),
City NVARCHAR(50)
);
CREATE TABLE Orders (
OrderId INT PRIMARY KEY,
CustomerId INT,
OrderDate DATE,
TotalAmount DECIMAL(10, 2)
);
-- Insert customers
INSERT INTO Customers VALUES (1, 'Amit Sharma', 'Mumbai');
INSERT INTO Customers VALUES (2, 'Priya Verma', 'Delhi');
INSERT INTO Customers VALUES (3, 'Rohit Gupta', 'Bangalore');
INSERT INTO Customers VALUES (4, 'Sneha Patil', 'Pune');
INSERT INTO Customers VALUES (5, 'Karan Mehta', 'Chennai');
-- Insert orders — notice customer 4 and 5 have no orders
-- and order 5 has a CustomerId 99 that doesn't exist in Customers
INSERT INTO Orders VALUES (1, 1, '2024-01-10', 1500.00);
INSERT INTO Orders VALUES (2, 1, '2024-02-15', 2200.00);
INSERT INTO Orders VALUES (3, 2, '2024-01-20', 800.00);
INSERT INTO Orders VALUES (4, 3, '2024-03-05', 3100.00);
INSERT INTO Orders VALUES (5, 99, '2024-03-10', 500.00);
Key things to notice here — Sneha (4) and Karan (5) have no orders at all. Order 5 has CustomerId 99 which doesn't exist in Customers. These edge cases are exactly what make the different join types matter.
Step 1 : INNER JOIN — Only Matching Rows
INNER JOIN returns rows where there is a match in both tables. If a customer has no orders, they don't appear. If an order has a CustomerId that doesn't exist in Customers, it doesn't appear either. Both sides must have a matching row.
SELECT
c.CustomerId,
c.CustomerName,
c.City,
o.OrderId,
o.OrderDate,
o.TotalAmount
FROM Customers c
INNER JOIN Orders o ON c.CustomerId = o.CustomerId
ORDER BY c.CustomerId;
Result :
| CustomerId | CustomerName | City | OrderId | OrderDate | TotalAmount |
|---|---|---|---|---|---|
| 1 | Amit Sharma | Mumbai | 1 | 2024-01-10 | 1500.00 |
| 1 | Amit Sharma | Mumbai | 2 | 2024-02-15 | 2200.00 |
| 2 | Priya Verma | Delhi | 3 | 2024-01-20 | 800.00 |
| 3 | Rohit Gupta | Bangalore | 4 | 2024-03-05 | 3100.00 |
Sneha and Karan are not here — they have no orders so there's no match. Order 5 (CustomerId 99) is also not here — no matching customer. Only 4 rows come back even though we have 5 customers and 5 orders.
Use INNER JOIN when you only want rows that have matching data on both sides. Most of the time this is what you want — show orders with their customer details, show products with their category, etc.
Step 2 : LEFT JOIN — All Rows from Left Table
LEFT JOIN returns all rows from the left table (Customers in our case) and the matching rows from the right table (Orders). If a customer has no orders, they still appear in the result — the order columns just show NULL.
SELECT
c.CustomerId,
c.CustomerName,
c.City,
o.OrderId,
o.OrderDate,
o.TotalAmount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
ORDER BY c.CustomerId;
Result :
| CustomerId | CustomerName | City | OrderId | OrderDate | TotalAmount |
|---|---|---|---|---|---|
| 1 | Amit Sharma | Mumbai | 1 | 2024-01-10 | 1500.00 |
| 1 | Amit Sharma | Mumbai | 2 | 2024-02-15 | 2200.00 |
| 2 | Priya Verma | Delhi | 3 | 2024-01-20 | 800.00 |
| 3 | Rohit Gupta | Bangalore | 4 | 2024-03-05 | 3100.00 |
| 4 | Sneha Patil | Pune | NULL | NULL | NULL |
| 5 | Karan Mehta | Chennai | NULL | NULL | NULL |
Now Sneha and Karan appear with NULL values in the order columns. Order 5 (CustomerId 99) still doesn't appear because it has no match in Customers — the left table — and LEFT JOIN only guarantees all rows from the left side.
This is probably the most commonly used join after INNER JOIN. Use it when you want all records from one table regardless of whether they have related data in the other table.
Real use case — show all customers and how many orders they've placed, including customers who haven't ordered anything yet.
SELECT
c.CustomerId,
c.CustomerName,
COUNT(o.OrderId) AS TotalOrders,
ISNULL(SUM(o.TotalAmount), 0) AS TotalSpent
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId, c.CustomerName
ORDER BY c.CustomerId;
Result :
| CustomerId | CustomerName | TotalOrders | TotalSpent |
|---|---|---|---|
| 1 | Amit Sharma | 2 | 3700.00 |
| 2 | Priya Verma | 1 | 800.00 |
| 3 | Rohit Gupta | 1 | 3100.00 |
| 4 | Sneha Patil | 0 | 0.00 |
| 5 | Karan Mehta | 0 | 0.00 |
With INNER JOIN here, Sneha and Karan wouldn't appear at all. LEFT JOIN keeps them with 0 orders and 0 spent. ISNULL converts NULL to 0 for the sum.
Step 3 : Find Rows with No Match Using LEFT JOIN
A very useful pattern — use LEFT JOIN with WHERE o.Column IS NULL to find customers who have never placed an order.
SELECT
c.CustomerId,
c.CustomerName,
c.City
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE o.OrderId IS NULL;
Result :
| CustomerId | CustomerName | City |
|---|---|---|
| 4 | Sneha Patil | Pune |
| 5 | Karan Mehta | Chennai |
This is a common interview question and a genuinely useful query. The WHERE o.OrderId IS NULL filters to only the rows where the LEFT JOIN found no match — meaning these customers have no orders at all.
Step 4 : RIGHT JOIN — All Rows from Right Table
RIGHT JOIN is the mirror of LEFT JOIN. It returns all rows from the right table (Orders) and matching rows from the left table (Customers). If an order has no matching customer, it still appears with NULL in the customer columns.
SELECT
c.CustomerId,
c.CustomerName,
c.City,
o.OrderId,
o.OrderDate,
o.TotalAmount
FROM Customers c
RIGHT JOIN Orders o ON c.CustomerId = o.CustomerId
ORDER BY o.OrderId;
Result :
| CustomerId | CustomerName | City | OrderId | OrderDate | TotalAmount |
|---|---|---|---|---|---|
| 1 | Amit Sharma | Mumbai | 1 | 2024-01-10 | 1500.00 |
| 1 | Amit Sharma | Mumbai | 2 | 2024-02-15 | 2200.00 |
| 2 | Priya Verma | Delhi | 3 | 2024-01-20 | 800.00 |
| 3 | Rohit Gupta | Bangalore | 4 | 2024-03-05 | 3100.00 |
| NULL | NULL | NULL | 5 | 2024-03-10 | 500.00 |
Now Order 5 appears with NULL in the customer columns because CustomerId 99 doesn't exist in Customers. Sneha and Karan don't appear because they have no orders — there's nothing in the right table (Orders) for them.
Honestly, RIGHT JOIN is used much less often than LEFT JOIN. Most developers just flip the table order and use LEFT JOIN instead. These two queries give identical results :
-- RIGHT JOIN
FROM Customers c
RIGHT JOIN Orders o ON c.CustomerId = o.CustomerId
-- Same result with LEFT JOIN, tables swapped
FROM Orders o
LEFT JOIN Customers c ON c.CustomerId = o.CustomerId
Pick whichever reads more naturally for your query.
Step 5 : FULL JOIN — All Rows from Both Tables
FULL JOIN (also written as FULL OUTER JOIN) returns all rows from both tables. Where there's a match, the columns are filled in. Where there's no match on either side, NULLs fill in the missing columns.
SELECT
c.CustomerId,
c.CustomerName,
c.City,
o.OrderId,
o.OrderDate,
o.TotalAmount
FROM Customers c
FULL JOIN Orders o ON c.CustomerId = o.CustomerId
ORDER BY c.CustomerId, o.OrderId;
Result :
| CustomerId | CustomerName | City | OrderId | OrderDate | TotalAmount |
|---|---|---|---|---|---|
| 1 | Amit Sharma | Mumbai | 1 | 2024-01-10 | 1500.00 |
| 1 | Amit Sharma | Mumbai | 2 | 2024-02-15 | 2200.00 |
| 2 | Priya Verma | Delhi | 3 | 2024-01-20 | 800.00 |
| 3 | Rohit Gupta | Bangalore | 4 | 2024-03-05 | 3100.00 |
| 4 | Sneha Patil | Pune | NULL | NULL | NULL |
| 5 | Karan Mehta | Chennai | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | 2024-03-10 | 500.00 |
All customers appear, all orders appear. Customers without orders show NULL for order columns. Order 5 with no matching customer shows NULL for customer columns.
FULL JOIN is useful for data reconciliation and auditing — find records in either table that don't have a match in the other. Like finding orphaned orders (no customer) and inactive customers (no orders) in one query.
-- Find unmatched rows on either side
SELECT
c.CustomerId,
c.CustomerName,
o.OrderId,
o.TotalAmount,
CASE
WHEN c.CustomerId IS NULL THEN 'Order has no customer'
WHEN o.OrderId IS NULL THEN 'Customer has no orders'
END AS Issue
FROM Customers c
FULL JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE c.CustomerId IS NULL OR o.OrderId IS NULL;
Result :
| CustomerId | CustomerName | OrderId | TotalAmount | Issue |
|---|---|---|---|---|
| 4 | Sneha Patil | NULL | NULL | Customer has no orders |
| 5 | Karan Mehta | NULL | NULL | Customer has no orders |
| NULL | NULL | 5 | 500.00 | Order has no customer |
Very handy for data quality checks.
Step 6 : Joining More Than Two Tables
Most real queries join three or more tables. Here's an example adding a Products table to the mix :
CREATE TABLE OrderItems (
ItemId INT PRIMARY KEY,
OrderId INT,
ProductName NVARCHAR(100),
Quantity INT,
UnitPrice DECIMAL(10, 2)
);
INSERT INTO OrderItems VALUES (1, 1, 'Laptop', 1, 1500.00);
INSERT INTO OrderItems VALUES (2, 2, 'Mouse', 2, 550.00);
INSERT INTO OrderItems VALUES (3, 2, 'Keyboard',1, 1100.00);
INSERT INTO OrderItems VALUES (4, 3, 'Monitor', 1, 800.00);
INSERT INTO OrderItems VALUES (5, 4, 'Headset', 2, 1550.00);
Now join all three tables :
SELECT
c.CustomerName,
c.City,
o.OrderId,
o.OrderDate,
oi.ProductName,
oi.Quantity,
oi.UnitPrice,
(oi.Quantity * oi.UnitPrice) AS LineTotal
FROM Customers c
INNER JOIN Orders o ON c.CustomerId = o.CustomerId
INNER JOIN OrderItems oi ON o.OrderId = oi.OrderId
ORDER BY c.CustomerName, o.OrderId;
Each JOIN adds another table to the result. The ON clause connects them — Orders to Customers via CustomerId, OrderItems to Orders via OrderId.
You can mix join types too. Use INNER JOIN between Customers and Orders (only customers with orders) and LEFT JOIN between Orders and OrderItems (show orders even if they somehow have no items) :
FROM Customers c
INNER JOIN Orders o ON c.CustomerId = o.CustomerId
LEFT JOIN OrderItems oi ON o.OrderId = oi.OrderId
Step 7 : Joins with GROUP BY and Aggregates
Real reporting queries combine joins with GROUP BY to summarise data across tables.
Total spent per customer per city :
SELECT
c.City,
COUNT(DISTINCT c.CustomerId) AS CustomerCount,
COUNT(o.OrderId) AS TotalOrders,
SUM(o.TotalAmount) AS TotalRevenue,
AVG(o.TotalAmount) AS AvgOrderValue
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
GROUP BY c.City
ORDER BY TotalRevenue DESC;
Monthly order summary :
SELECT
FORMAT(o.OrderDate, 'yyyy-MM') AS Month,
COUNT(o.OrderId) AS OrderCount,
SUM(o.TotalAmount) AS MonthlyRevenue,
COUNT(DISTINCT o.CustomerId) AS UniqueCustomers
FROM Orders o
INNER JOIN Customers c ON c.CustomerId = o.CustomerId
GROUP BY FORMAT(o.OrderDate, 'yyyy-MM')
ORDER BY Month;
Common Mistakes
Getting duplicate rows
If there's a one-to-many relationship and you forget about it, joining can multiply rows. Customer 1 has 2 orders — so joining Customers to Orders gives you 2 rows for Customer 1. If you then also join to a table that has 3 rows per order, you get 6 rows for Customer 1. Be aware of your data relationships and use GROUP BY or subqueries when needed.
Using WHERE instead of ON for LEFT JOIN conditions
This is a subtle but important mistake. If you add a filter on the right table in the WHERE clause of a LEFT JOIN, it turns it into an INNER JOIN effectively :
-- WRONG — this behaves like INNER JOIN because WHERE filters out NULLs
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE o.Status = 'Pending'; -- Customers with no orders are excluded
-- CORRECT — filter in the ON clause keeps all customers
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId AND o.Status = 'Pending';
Put filters for the right table in the ON clause, not the WHERE clause, when you want to keep all left table rows.
Forgetting table aliases
When joining multiple tables that have columns with the same name — like CustomerId appearing in both Customers and Orders — always use table aliases. Without aliases SQL Server throws an ambiguous column name error.
Quick Reference — Which JOIN to Use
| Scenario | JOIN Type |
|---|---|
| Only rows with matches on both sides | INNER JOIN |
| All left table rows + matching right | LEFT JOIN |
| All right table rows + matching left | RIGHT JOIN |
| All rows from both tables | FULL JOIN |
| Find rows with no match (left only) | LEFT JOIN + WHERE right.col IS NULL |
| Find rows with no match (right only) | RIGHT JOIN + WHERE left.col IS NULL |
| Find unmatched rows on either side | FULL JOIN + WHERE either IS NULL |
Summary
You learned how SQL joins work and when to use each type. You covered :
- INNER JOIN — only returns rows with a match in both tables
- LEFT JOIN — returns all left table rows, NULLs where no match in right table
- Using LEFT JOIN with WHERE IS NULL to find rows with no match
- RIGHT JOIN — returns all right table rows, NULLs where no match in left table
- FULL JOIN — returns all rows from both tables, NULLs on either side where no match
- Joining three tables together with multiple JOIN clauses
- Combining joins with GROUP BY and aggregate functions for reports
- Common mistakes — duplicate rows, WHERE vs ON for LEFT JOIN filters, missing aliases
Joins are something you use in almost every SQL query once your database has more than one table. Get comfortable with LEFT JOIN and INNER JOIN first — those two cover 90% of what you'll need. Then FULL JOIN and RIGHT JOIN make sense once you encounter data reconciliation or audit scenarios.
I hope you like this article...
Happy coding! 🚀
0 Comments