INNER JOIN

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

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 Oracle: 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. Every order that has a matching customer. 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 Oracle — 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 c, o) keep multi-table queries readable
• You can join more than two tables by chaining additional JOIN clauses

-- Basic INNER JOIN: combine orders with customers.
-- Only orders that have a matching customer are returned.
-- c and o are table aliases -- shorthand to avoid repeating full table names.
SELECT
    o.order_id,
    TO_CHAR(o.order_date, 'YYYY-MM-DD')      AS order_date,
    o.total_amount,
    o.status,
    c.first_name || ' ' || c.last_name       AS customer_name,  -- || is Oracle concatenation
    c.country
FROM   dataplexa_store.orders    o
INNER JOIN dataplexa_store.customers c ON o.customer_id = c.customer_id
ORDER  BY o.order_date DESC;

• Every row in the result has a valid customer_name — INNER JOIN only returns rows where o.customer_id matched a c.customer_id
• Any order whose customer_id had no match in customers would be silently excluded
• Sarah Chen appears twice — she placed orders 1042 and 1019, which is correct one-to-many behaviour
• TO_CHAR(o.order_date, 'YYYY-MM-DD') formats the Oracle DATE — without it Oracle shows 18-JUN-24 by default

INNER JOIN Across Three Tables

You chain JOIN clauses to bring in additional tables. Each JOIN adds one more table to the result set. SQL Server and Oracle handle this identically — start from the natural focus of the query and join outward to the tables that provide context. Oracle 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 → order_items → products.
-- This traverses the many-to-many through the junction table.
SELECT
    o.order_id,
    TO_CHAR(o.order_date, 'YYYY-MM-DD')      AS order_date,
    c.first_name || ' ' || c.last_name       AS customer_name,
    p.product_name,
    p.category,
    oi.quantity,
    oi.price                                 AS unit_price,
    oi.quantity * oi.price                   AS line_total
FROM   dataplexa_store.orders       o
INNER JOIN dataplexa_store.customers    c  ON o.customer_id  = c.customer_id
INNER JOIN dataplexa_store.order_items  oi ON o.order_id     = oi.order_id
INNER JOIN dataplexa_store.products     p  ON oi.product_id  = p.product_id
WHERE  o.order_id = 1042
ORDER  BY line_total DESC;

• Four tables are touched — orders, customers, order_items, products — connected by three JOIN clauses
• Each JOIN adds columns from the new table; the next JOIN can reference any column already in the working set
• The customer name repeats on every line — the JOIN correctly multiplies the order row against each of its line items
• WHERE runs after all JOINs complete and filters the fully combined row set down to order 1042

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 with single-table queries — the only difference is that columns from multiple tables are now available at every stage.

-- Which customers spent more than 500 in 2024?
-- EXTRACT(YEAR FROM col) is Oracle's equivalent of SQL Server's YEAR()
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name  AS customer_name,
    c.country,
    COUNT(o.order_id)                   AS total_orders,
    SUM(o.total_amount)                 AS total_spent,
    ROUND(AVG(o.total_amount), 2)       AS avg_order,
    MIN(o.order_date)                   AS first_order,
    MAX(o.order_date)                   AS last_order
FROM   dataplexa_store.customers  c
INNER JOIN dataplexa_store.orders o ON c.customer_id = o.customer_id
WHERE  EXTRACT(YEAR FROM o.order_date) = 2024   -- EXTRACT replaces SQL Server's YEAR()
GROUP  BY c.customer_id, c.first_name, c.last_name, c.country
HAVING SUM(o.total_amount) > 500
ORDER  BY total_spent DESC;

• INNER JOIN automatically excludes customers with zero 2024 orders — no need for a HAVING clause to remove them separately
• EXTRACT(YEAR FROM o.order_date) is Oracle's equivalent of SQL Server's YEAR(o.OrderDate)
• WHERE filters rows before grouping — only 2024 order rows enter the GROUP BY step
• GROUP BY must list all non-aggregated SELECT columns — Oracle raises ORA-00979 if any are missing

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. 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 large tables.

-- 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.product_name,
    p.category,
    p.price,
    pr.promo_name,
    pr.discount_pct,
    ROUND(p.price * (1 - pr.discount_pct / 100), 2)  AS discounted_price
FROM   dataplexa_store.products    p
INNER JOIN dataplexa_store.promotions pr ON p.category = pr.category
WHERE  pr.active = 1
ORDER  BY p.category, discounted_price;

• 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
• Non-key joins are powerful but require care — always verify the row count is what you expect

Common INNER JOIN Mistakes

Several mistakes appear repeatedly when first writing JOIN queries. Forgetting the ON clause raises an error. Referencing an ambiguous column that exists in both tables without an alias prefix raises ORA-00918 in Oracle. Assuming INNER JOIN will return all rows when some foreign keys are NULL or orphaned leads to a silently lower row count — the only symptom is fewer rows than expected.

-- MISTAKE: ambiguous column -- customer_id exists in both orders and customers
-- Oracle raises ORA-00918: column ambiguously defined
SELECT order_id, customer_id, first_name      -- which customer_id? orders or customers?
FROM   dataplexa_store.orders
INNER JOIN dataplexa_store.customers ON orders.customer_id = customers.customer_id;
-- CORRECT: prefix every column with its table alias
SELECT o.order_id, o.customer_id, c.first_name
FROM   dataplexa_store.orders    o
INNER JOIN dataplexa_store.customers c ON o.customer_id = c.customer_id;
-- Detecting silent row loss: compare COUNT from base table to JOIN result
SELECT COUNT(*) AS total_orders FROM dataplexa_store.orders;       -- 148 rows
SELECT COUNT(*) AS joined_orders                                    -- 145 rows -- 3 missing
FROM   dataplexa_store.orders    o
INNER JOIN dataplexa_store.customers c ON o.customer_id = c.customer_id;

• Oracle raises ORA-00918: column ambiguously defined — SQL Server raises Msg 209 with the same meaning
• Always prefix column names with the table alias — o.customer_id not just customer_id
• Comparing COUNT(*) from the base table against the joined result is the fastest way to detect silent row loss
• 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

Lesson Summary

Practice Questions

Quiz

Next up — LEFT, RIGHT & FULL OUTER JOIN — How each outer join type preserves unmatched rows, when to choose one over another, and the Oracle syntax difference for FULL OUTER JOIN.