A join combines rows from two tables by matching a condition — usually a foreign key equal to a primary key. INNER JOIN keeps only the rows that match on both sides.

Why joins exist

Normalised tables store each fact once: an employee row holds dept_id = 1, not the text Engineering, Bengaluru. A join puts the pieces back together at query time.

Join	Returns
`INNER JOIN`	Only matching pairs
`LEFT [OUTER] JOIN`	All left rows + matches (`NULL`s where none)
`RIGHT [OUTER] JOIN`	All right rows + matches
`FULL [OUTER] JOIN`	All rows from both sides
`CROSS JOIN`	Every combination (Cartesian product)
Self join	A table joined to itself (hierarchies, comparisons)

INNER JOIN

SQL

SELECT e.name, d.name AS department, d.location
FROM employees e
INNER JOIN departments d ON d.dept_id = e.dept_id
ORDER BY e.emp_id;

Output

name   | department  | location
-------+-------------+----------
Asha   | Engineering | Bengaluru
Ravi   | Engineering | Bengaluru
Meera  | Engineering | Bengaluru
Karan  | Engineering | Bengaluru
Priya  | Sales       | Mumbai
Arjun  | Sales       | Mumbai
Neha   | Sales       | Mumbai
Vikram | HR          | Hyderabad
Sara   | HR          | Hyderabad

10 employees, but only 9 rows: Dev (dept_id NULL) matches no department, and Marketing matches no employee — an inner join drops both. JOIN alone means INNER JOIN.

How a join is evaluated (conceptually)

Pair up
Take every combination of a row from the left table with a row from the right table.
Keep matches
Keep only pairs for which the ON condition is true.
Continue
Further joins, WHERE, GROUP BY… work on this combined row set.

Joining several tables

SQL

-- what did each customer buy? (customers → orders → order_items → products)
SELECT c.name AS customer, o.order_id, p.name AS product, oi.quantity
FROM customers c
JOIN orders o       ON o.customer_id = c.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.status = 'delivered'
ORDER BY o.order_id, p.name;

Output

customer | order_id | product    | quantity
---------+----------+------------+---------
Ananya   | 101      | Headphones | 1
Ananya   | 101      | Laptop     | 1
Rahul    | 102      | Chair      | 2
Rahul    | 102      | Pen        | 10
Ananya   | 103      | Notebook   | 5
Ananya   | 103      | Pen        | 5
Rahul    | 106      | Laptop     | 1

ON vs USING vs NATURAL

SQL

-- USING: the column has the same name on both sides; it appears once in the output
SELECT order_id, customer_id, name, status
FROM orders JOIN customers USING (customer_id)
WHERE order_id <= 102
ORDER BY order_id;

Output

order_id | customer_id | name   | status
---------+-------------+--------+----------
101      | 1           | Ananya | delivered
102      | 2           | Rahul  | delivered

Join condition vs filter

For an inner join, a condition in ON or in WHERE gives the same result. Convention: relationships in ON, row filters in WHERE. For outer joins the placement changes the result — see the next topic.

What is the difference between JOIN and INNER JOIN?

Can you join on columns that are not keys?