INNER JOIN

Combine rows from two tables where a matching condition is met.

Overview

INNER JOIN returns rows that have matching values in both tables. It is the most common type of join and is usually what people mean when they say "JOIN" without a qualifier. Rows from either table that have no match in the other are excluded from the result. JOINs are the cornerstone of relational databases they let you query normalised data spread across multiple tables as if it were one flat result set. Understanding how to write efficient JOINs (using indexed columns, choosing the right join type) is essential for working with any real schema.

Key Points

•INNER JOIN = JOIN INNER is the default and can be omitted
•Returns only rows where the join condition is true in BOTH tables
•Unmatched rows are excluded use LEFT JOIN to keep them
•JOIN on indexed foreign key columns for best performance
•Can join more than two tables: FROM a JOIN b ON ... JOIN c ON ...

Examples

Basic INNER JOIN

SELECT o.id AS order_id, c.name AS customer, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

Multi-table JOIN

SELECT
  o.id       AS order_id,
  c.name     AS customer,
  p.name     AS product,
  oi.quantity,
  oi.unit_price
FROM orders o
JOIN customers c  ON c.id  = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p   ON p.id  = oi.product_id
WHERE o.status = 'shipped';

INNER JOIN

Overview

Key Points

Examples

Exercises

Knowledge Check

What happens to a customer who has no orders when you use INNER JOIN between customers and orders?