INNER JOIN is the most common join. It returns only the rows that have a match in both tables. Think of it like a Venn diagram — you only get the overlapping section. If a customer has no orders, they do not appear. If an order has no matching customer (somehow), it does not appear either.
-- Show all orders with customer names
SELECT
c.customer_id,
c.first_name,
c.last_name,
o.order_id,
o.total_amount,
o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- The ON clause defines the relationship
-- c.customer_id = o.customer_id means:
-- "match rows where the customer_id in customers
-- equals the customer_id in orders"| customers | orders | ||
|---|---|---|---|
| customer_id=1 (Priya) | order_id=101, customer_id=1 | ||
| customer_id=2 (Amit) | order_id=102, customer_id=1 | ||
| customer_id=3 (Sneha) | order_id=103, customer_id=2 | ||
| order_id=104, customer_id=99 | (no match!) |
With INNER JOIN: Priya appears twice (she has 2 orders). Amit appears once. Sneha has no orders — she is excluded. Order 104 references customer_id=99 which does not exist — also excluded. Only matching pairs survive.
-- QA scenario: Verify orders placed today have valid customers
SELECT
c.first_name,
c.last_name,
c.email,
o.order_id,
o.total_amount,
o.status
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date = CURRENT_DATE
ORDER BY o.total_amount DESC;
-- Count orders per customer (only customers who have orders)
SELECT
c.first_name,
c.last_name,
COUNT(o.order_id) AS total_orders,
SUM(o.total_amount) AS lifetime_value
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY lifetime_value DESC;INNER JOIN silently drops rows with no match. If your result set has fewer rows than you expect, it might be because some rows do not have a matching record in the other table. Always double-check the row count.
Q: What is the difference between JOIN and INNER JOIN?
A: They are the same thing. Writing JOIN without a prefix defaults to INNER JOIN. Most teams write INNER JOIN explicitly for clarity, especially in code reviews. It makes the intent obvious to anyone reading the query.
Key Point: INNER JOIN returns only rows that match in both tables. No match = no row in the result. It is the default JOIN type.