A well-designed database splits information across tables. Orders keep a CustomerID, not the customer’s name; order lines keep a ProductID, not the product’s price. That avoids repeated data, but it means a useful report – “who ordered what, and for how much?” – needs data from several tables at once. A join puts those rows back together, and this lesson covers the SQL joins you will use most.
INNER JOIN: matching rows only
A join needs two things: the tables to combine and the rule that links them, written after ON. Here each order is linked to the customer whose CustomerID matches:
SELECT o.OrderID, o.OrderDate, c.CustomerName, c.City
FROM Orders AS o
INNER JOIN Customers AS c
ON o.CustomerID = c.CustomerID
ORDER BY o.OrderID;Returns 10 rows, one per order; order 1001 belongs to Annapoorna Stores in Chennai.
o and c are table aliases: short names that save typing. Writing o.CustomerID and c.CustomerID tells the database which table each column comes from. This is required when both tables have a column with the same name, and it makes longer queries easier to follow. The word INNER is optional – plain JOIN means the same thing.
An inner join keeps only rows that have a match in both tables. Customers who have never ordered do not appear, because there is no order row to pair them with.
LEFT JOIN: keep everything from the first table
A LEFT JOIN (also written LEFT OUTER JOIN) keeps every row from the table on the left, even when nothing matches on the right. Where there is no match, the right-hand columns are filled with NULL. That makes it perfect for finding things that are missing, such as customers with no orders:
SELECT c.CustomerName, c.City
FROM Customers AS c
LEFT JOIN Orders AS o
ON o.CustomerID = c.CustomerID
WHERE o.OrderID IS NULL;Returns 4 rows: Ganga Provisions, Indus Grocers, Kaveri Wholesale and Lotus Organics.
Remove the WHERE line and run it again: you will see all twelve customers, with order columns empty for these four. Customers with two orders appear twice – a join produces one row per matching pair.
RIGHT JOIN and FULL OUTER JOIN
| Join | Keeps | SQLite | MySQL | SQL Server |
|---|---|---|---|---|
INNER JOIN | Matches only | Yes | Yes | Yes |
LEFT JOIN | All left rows | Yes | Yes | Yes |
RIGHT JOIN | All right rows | Since 3.39 (2022) | Yes | Yes |
FULL OUTER JOIN | All rows from both | Since 3.39 (2022) | No – combine a LEFT and a RIGHT join with UNION | Yes |
A RIGHT JOIN is simply a LEFT JOIN with the tables swapped, so most developers stick to LEFT JOIN and put the “keep everything” table first. Older SQLite versions you may find in tools or phones do not support RIGHT or FULL joins at all.
Which join should I use?
Ask yourself one question: should rows without a match still appear? If no – for example, an invoice only needs orders that really have a customer – use INNER JOIN. If yes – a list of all customers with their order count, including zero – use LEFT JOIN and start from the table whose rows must all be shown.
Joining three or more tables
Add one JOIN … ON … for each extra table. To list what was in order 1001, start from OrderDetails, join Products for the name and price, and calculate a line total:
SELECT od.OrderID,
p.ProductName,
od.Quantity,
p.Price,
od.Quantity * p.Price AS LineTotal
FROM OrderDetails AS od
JOIN Products AS p ON p.ProductID = od.ProductID
WHERE od.OrderID = 1001;Returns 2 rows: 10 × Filter Coffee Powder (2800) and 4 × Ponni Rice (1680).
SQL joins with GROUP BY
Joins and GROUP BY work well together. This query follows the chain Customers → Orders → OrderDetails → Products, ignores cancelled orders, and adds up how much each customer has spent:
SELECT c.CustomerName,
SUM(od.Quantity * p.Price) AS TotalSpent
FROM Customers AS c
JOIN Orders AS o ON o.CustomerID = c.CustomerID
JOIN OrderDetails AS od ON od.OrderID = o.OrderID
JOIN Products AS p ON p.ProductID = od.ProductID
WHERE o.Status <> 'Cancelled'
GROUP BY c.CustomerID, c.CustomerName
ORDER BY TotalSpent DESC;Returns 8 rows. Coimbatore Mart is the top spender with 6750; Harbour Foods is last with 1710.
We group by c.CustomerID as well as the name. Two different shops could share a name, but never an ID, so grouping by the key keeps their totals apart. The four customers without orders are missing here because these are inner joins; switch the first one to a LEFT JOIN and wrap the sum in COALESCE(…, 0) if you want them listed with zero.
Self join: a table joined to itself
In Employees, ManagerID points to another row in the same table. To show each person next to their manager’s name, join the table to itself with two different aliases. We use a LEFT JOIN so Karthik, who has no manager, still appears.
SELECT e.FirstName AS Employee,
m.FirstName AS Manager
FROM Employees AS e
LEFT JOIN Employees AS m
ON e.ManagerID = m.EmployeeID;Returns 8 rows. Divya, Sneha and Rahul report to Karthik; Nisha and Ajay report to Rahul; Karthik's manager is NULL.
Watch out: Forgetting the ON condition (or getting it wrong) pairs every row with every other row – a cross join. Ten orders × twelve customers gives 120 rows of nonsense. If a join returns far more rows than you expected, check the ON clause first.
Next, SQL LIKE and Wildcards shows how to search text by pattern. In a PHP site, SQL joins like these are what you run through PDO to build order pages and reports.