SQL Joins and INNER JOIN

A join combines rows from two tables by matching values in a related column. ShopKart keeps orders in one table and customers in another. To show who placed each order, you join orders to customers on customer_id.

INNER JOIN keeps only rows that have a match in both tables.

Why data is split across tables

Storing the customer's name on every order would repeat it dozens of times, and changing it would mean updating every order. Instead, each order stores only customer_id, and the name lives once in customers. Joins put the pieces back together when you query. (This design is called normalization.)

Syntax

SELECT a.column, b.column
FROM table_a AS a
INNER JOIN table_b AS b
  ON a.key = b.key;

Types of joins at a glance

This lesson covers INNER JOIN. The others follow in the next lessons.

Examples

1. Orders with customer names

SELECT o.order_id,
       o.order_date,
       c.customer_name,
       c.city
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.customer_id
ORDER BY o.order_id
LIMIT 5;

Result:

(5 rows)

For each order, SQL finds the customer row with the same customer_id and glues the two rows together.

2. Order lines with product names

SELECT oi.order_id,
       p.product_name,
       oi.quantity,
       oi.unit_price
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
ORDER BY oi.order_id
LIMIT 5;

Result:

(5 rows)

JOIN on its own means INNER JOIN, and AS is optional for table aliases.

3. Join, then aggregate

Revenue by product name, the query we couldn't write in the GROUP BY lesson:

SELECT p.product_name,
       SUM(oi.quantity) AS units,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)), 2) AS net_revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY net_revenue DESC
LIMIT 5;

Result:

(5 rows)

4. Join with a filter

Delivered orders from Prime members:

SELECT o.order_id, c.customer_name, o.order_date
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE c.is_prime
  AND o.status = 'Delivered'
ORDER BY o.order_date
LIMIT 6;

Result:

(6 rows)

5. USING as a shortcut

When the join column has the same name in both tables:

SELECT order_id, customer_name
FROM orders
JOIN customers USING (customer_id)
LIMIT 3;

Result:

(3 rows)

INNER JOIN drops unmatched rows

ShopKart has 12 customers, but Divya Menon has never ordered. An inner join from customers to orders leaves her out:

SELECT COUNT(DISTINCT c.customer_id) AS customers_in_result
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

Result:

(1 row)

That's correct for "customers who ordered" but wrong for "all customers and their order count". For that you need a LEFT JOIN.

Common mistakes

Ambiguous column names. When both tables have customer_id, say which one:

SELECT customer_id, customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Error: column reference "customer_id" is ambiguous

Fix: SELECT o.customer_id, ....

• Joining on the wrong column. ON o.order_id = c.customer_id runs without error and returns nonsense. Always join a foreign key to the key it references.
• Forgetting that joins multiply rows. One order with three items becomes three rows after joining to order_items. Summing an order-level value afterwards double counts.

Practice question

List each order line with the customer's name and the product's name for order 1001. (Hint: you need three tables. Joining more than two is covered in lesson 20, but try it.)

SELECT o.order_id, c.customer_name, p.product_name, oi.quantity
FROM orders o
JOIN customers c   ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p    ON p.product_id = oi.product_id
WHERE o.order_id = 1001;

Key takeaways

• Joins combine tables by matching related columns.
• INNER JOIN (or just JOIN) returns only matching rows.
• Use table aliases and prefix columns that exist in both tables.
• Joins can multiply rows; check counts before you sum.