INNER JOIN

What Is an INNER JOIN?

An INNER JOIN combines rows from two tables by matching a column in one table against a column in the other. Only rows where the match condition is true on both sides are included in the result. If a row in the left table has no matching row in the right table — or vice versa — that row is silently excluded. INNER JOIN is the default JOIN type in SQL Server: writing JOIN without any keyword is identical to writing INNER JOIN.

The result of an INNER JOIN is the intersection of the two tables based on the ON condition. Think of it as asking: "give me only the rows where both sides agree." Every order that has a matching user. Every product that has a matching category. Every order item that has a matching product. Rows without a counterpart on the other side simply do not appear — they are not errors, they are just excluded. This is different from a LEFT JOIN, which would keep them.

• INNER JOIN returns only rows that have a match in both tables
• Rows with no match on either side are silently excluded from the result
• JOIN and INNER JOIN are identical in SQL Server — the INNER keyword is optional
• The ON clause defines the match condition — usually a primary key equalling a foreign key
• Table aliases (short names like u, o) keep multi-table queries readable
• You can join more than two tables by chaining additional JOIN clauses

-- Basic INNER JOIN: combine Orders with Users.
-- Only orders that have a matching user in the Users table are returned.
-- u and o are table aliases -- shorthand to avoid repeating full table names.
SELECT
    o.OrderId,
    o.OrderDate,
    o.Total,
    o.Status,
    u.FirstName + ' ' + u.LastName  AS CustomerName,  -- column from Users
    u.Country                                          -- column from Users
FROM Orders o                        -- left table with alias o
INNER JOIN Users u                   -- right table with alias u
    ON o.UserId = u.UserId           -- match condition: FK in Orders = PK in Users
ORDER BY o.OrderDate DESC;

• Every row in the result has a valid CustomerName — because INNER JOIN only returns rows where o.UserId matched a u.UserId
• Any order whose UserId had no match in Users (e.g. a data integrity gap) would be silently excluded
• Sarah Chen appears twice — she placed orders 1042 and 1019, which is correct one-to-many behaviour
• The alias o.OrderDate and u.Country disambiguate which table each column comes from — essential when both tables share column names like UserId

INNER JOIN Across Three Tables

You chain JOIN clauses to bring in additional tables. Each JOIN adds one more table to the result set. The order matters for readability — start from the table that is the natural focus of the query and join outward to the tables that provide context. SQL Server processes all JOINs before applying WHERE, GROUP BY, or ORDER BY, so the full combined row set is available for any subsequent filtering or aggregation.

-- Three-table INNER JOIN: Orders → OrderItems → Products.
-- This is the many-to-many traversal through the junction table.
-- Each JOIN adds one more table to the working row set.
SELECT
    o.OrderId,
    o.OrderDate,
    u.FirstName + ' ' + u.LastName  AS CustomerName,
    p.Name                          AS ProductName,
    p.Category,
    oi.Quantity,
    oi.Price                        AS UnitPrice,
    oi.Quantity * oi.Price          AS LineTotal
FROM Orders     o
INNER JOIN Users     u  ON o.UserId    = u.UserId     -- Orders + customer details
INNER JOIN OrderItems oi ON o.OrderId  = oi.OrderId   -- Orders + line items
INNER JOIN Products   p  ON oi.ProductId = p.ProductId -- line items + product details
WHERE o.OrderId = 1042
ORDER BY LineTotal DESC;

• Four tables are touched in this query — Orders, Users, OrderItems, Products — connected by three JOIN clauses
• Each JOIN adds columns from the new table to the working row set; the next JOIN can reference any column already in that set
• The customer name (Sarah Chen) repeats on every line — this is correct, the JOIN multiplies the order row against each of its line items
• The WHERE runs after all JOINs complete — it then filters the fully combined row set down to order 1042 only

INNER JOIN with WHERE, GROUP BY, and HAVING

INNER JOIN works seamlessly with every other clause. The JOIN produces a combined row set, then WHERE filters individual rows, then GROUP BY collapses them into groups, then HAVING filters the groups, and finally ORDER BY sorts the output. The full clause pipeline applies exactly as it does with single-table queries — the only difference is that columns from multiple tables are now available at every stage.

-- INNER JOIN combined with GROUP BY and HAVING.
-- Which customers spent more than $500 in 2024?
SELECT
    u.UserId,
    u.FirstName + ' ' + u.LastName  AS CustomerName,
    u.Country,
    COUNT(o.OrderId)                AS TotalOrders,
    SUM(o.Total)                    AS TotalSpent,
    ROUND(AVG(o.Total), 2)          AS AvgOrder,
    MIN(o.OrderDate)                AS FirstOrder,
    MAX(o.OrderDate)                AS LastOrder
FROM Users  u
INNER JOIN Orders o ON u.UserId = o.UserId          -- only users who have orders
WHERE YEAR(o.OrderDate) = 2024                      -- 2024 orders only
GROUP BY u.UserId, u.FirstName, u.LastName, u.Country
HAVING SUM(o.Total) > 500                           -- only high-value customers
ORDER BY TotalSpent DESC;

• INNER JOIN here means customers with zero 2024 orders are automatically excluded — no need for a HAVING clause to remove them separately
• WHERE filters rows before grouping — only 2024 order rows enter the GROUP BY step
• HAVING then removes any customer group whose total is $500 or less
• The GROUP BY must list all non-aggregated SELECT columns: UserId, FirstName, LastName, Country

INNER JOIN on Non-Key Columns

The ON clause can match any two columns with compatible data types — the join does not have to be on primary and foreign keys. You can join on category names, country codes, date ranges, or any shared value. However, joining on non-indexed columns is much slower on large tables, and joining on non-unique columns multiplies rows — every matching combination appears as a separate result row. Always understand the cardinality of a join before running it on production data.

-- INNER JOIN on a non-key column: match products to promotions by Category.
-- One category can match many promotions -- rows multiply per match.
SELECT
    p.Name          AS ProductName,
    p.Category,
    p.Price,
    pr.PromoName,
    pr.DiscountPct,
    ROUND(p.Price * (1 - pr.DiscountPct / 100.0), 2)  AS DiscountedPrice
FROM Products   p
INNER JOIN Promotions pr ON p.Category = pr.Category   -- join on category name, not PK/FK
WHERE pr.Active = 1                                    -- only live promotions
ORDER BY p.Category, DiscountedPrice;

• The join is on p.Category = pr.Category — a shared text value, not a primary/foreign key pair
• If Electronics had two active promotions, every Electronics product would appear twice — one row per matching promotion
• ROUND(p.Price * (1 - pr.DiscountPct / 100.0), 2) calculates the discounted price inline — the 1.0 forces decimal division instead of integer division
• Non-key joins are powerful but require care — always verify the row count is what you expect before using the result

Common INNER JOIN Mistakes

Several mistakes appear repeatedly when developers first write JOIN queries. The most common is forgetting the ON clause — SQL Server raises an error. The second is specifying an ambiguous column name that exists in both tables without a table alias prefix — SQL Server raises an error. The third is assuming an INNER JOIN will return all rows when some foreign keys are NULL or orphaned — those rows are silently dropped and the only symptom is a lower row count than expected.

-- Common mistakes and their corrections.
-- MISTAKE 1: Ambiguous column -- UserId exists in both Orders and Users
-- SQL Server raises: "Ambiguous column name 'UserId'"
SELECT OrderId, UserId, FirstName        -- which UserId? Orders.UserId or Users.UserId?
FROM Orders
INNER JOIN Users ON Orders.UserId = Users.UserId;
-- CORRECT: prefix every column with its table alias
SELECT o.OrderId, o.UserId, u.FirstName  -- now unambiguous
FROM Orders o
INNER JOIN Users u ON o.UserId = u.UserId;
-- MISTAKE 2: Expecting all rows when some FK values are NULL or missing
-- If 3 orders have NULL UserId, they will silently disappear from the INNER JOIN result
-- Diagnosis: compare counts
SELECT COUNT(*) AS TotalOrders FROM Orders;           -- 148 rows
SELECT COUNT(*) AS JoinedOrders                       -- 145 rows -- 3 missing
FROM Orders o
INNER JOIN Users u ON o.UserId = u.UserId;
-- The 3-row gap reveals orphaned orders (UserId NULL or referencing a deleted user)

• Always prefix column names with the table alias — o.UserId not just UserId — especially when joining tables that share column names
• Comparing COUNT(*) from the base table against the joined result is the fastest way to detect silent row loss from INNER JOIN
• A gap between the two counts almost always means NULL foreign keys or orphaned rows — investigate with a LEFT JOIN to identify the specific missing rows
• Use WHERE o.UserId IS NULL after a LEFT JOIN to surface the exact rows that INNER JOIN dropped

Lesson Summary

Practice Questions

Quiz

Next up — LEFT, RIGHT & FULL JOIN - How each outer join type preserves unmatched rows and when to choose one over another.