Joins: INNER JOIN and Table Aliases

A well-designed database splits its data across tables: a customer's name and city are stored once in customers, and every order only carries a customer_id pointing there (see Tables, Keys and Relationships). That keeps the data correct, but the questions people ask cross those tables: "which city do our orders come from?", "which category sells best?". A join puts the pieces back together for one query, without copying anything.

For AI and data work, joins are everyday tools. A training table for a churn model needs each customer's details next to their order history; an evaluation report needs each model answer next to the prompt and the user who sent it; a RAG system filters document chunks by metadata stored in another table. All of these are joins.

What you will learn

• Why joins exist and how a join matches rows, step by step
• INNER JOIN … ON, its short form JOIN, and USING
• Table aliases and why you should qualify every column in a join
• The "ambiguous column" error in SQLite, MySQL and PostgreSQL
• Joins together with WHERE, GROUP BY and ORDER BY, and the same join in pandas

Why joins: the information is in two places

Look at the first orders on their own:

SELECT order_id, customer_id, order_date, status
FROM orders
ORDER BY order_id
LIMIT 4;

+----------+-------------+------------+-----------+
| order_id | customer_id | order_date | status    |
+----------+-------------+------------+-----------+
|        1 |           1 | 2026-01-12 | delivered |
|        2 |           2 | 2026-01-20 | delivered |
|        3 |           1 | 2026-02-03 | delivered |
|        4 |           3 | 2026-02-14 | delivered |
+----------+-------------+------------+-----------+

Who is customer 1? The order does not say: it only holds the foreign key customer_id. The name lives in customers, in the row whose primary key is 1. A join follows that pointer for every row at once.

How a join matches rows

Take four customers and four orders. The join condition is orders.customer_id = customers.customer_id.

Before — two separate tables:

After INNER JOIN — one row for every pair that satisfies the condition:

Three things to notice, because they explain almost every join surprise you will meet:

• A row can match several times. Nadia has two orders, so her name appears twice. The "one" side of a one-to-many relationship is repeated for each row on the "many" side.
• Rows with no partner disappear. Karim (customer 8) has no orders, so an inner join leaves him out completely. No error, no warning.
• The result is a new table with columns from both sides. You can filter, group and sort it like any other table.

A useful mental model: the database considers every pair (customer, order) and keeps the pairs where the ON condition is true. Real databases use indexes and hash tables so they do not literally try every pair, but the result is exactly as if they did.

customers (4 rows)  ×  orders (4 rows)  =  16 possible pairs
                       keep pairs where orders.customer_id = customers.customer_id
                    →  4 rows

INNER JOIN … ON

SELECT orders.order_id, orders.order_date, customers.name, customers.city
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
ORDER BY orders.order_id
LIMIT 5;

+----------+------------+---------------+------------+
| order_id | order_date | name          | city       |
+----------+------------+---------------+------------+
|        1 | 2026-01-12 | Nadia Rahman  | Dhaka      |
|        2 | 2026-01-20 | Tanvir Ahmed  | Chattogram |
|        3 | 2026-02-03 | Nadia Rahman  | Dhaka      |
|        4 | 2026-02-14 | Farhana Akter | Dhaka      |
|        5 | 2026-02-25 | Rafiq Islam   | Sylhet     |
+----------+------------+---------------+------------+

• FROM orders INNER JOIN customers names the two tables. ON … says which rows belong together.
• orders.order_id is a qualified column: table name, dot, column name. It says exactly which table the column comes from.
• INNER is optional: plain JOIN means INNER JOIN in every dialect. Most people write JOIN.
• The join happens first (it is part of FROM), then WHERE, GROUP BY, SELECT and ORDER BY work on the joined rows — the logical order from How SQL Works: Statements, Command Types and Query Order.

Table aliases

Writing customers. before every column gets long. A table alias gives a table a short name for the duration of the query:

SELECT o.order_id, o.status, c.name
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
WHERE c.city = 'Dhaka'
ORDER BY o.order_id;

+----------+-----------+---------------+
| order_id | status    | name          |
+----------+-----------+---------------+
|        1 | delivered | Nadia Rahman  |
|        3 | delivered | Nadia Rahman  |
|        4 | delivered | Farhana Akter |
|        8 | delivered | Farhana Akter |
|       10 | delivered | Nadia Rahman  |
|       11 | delivered | Mitu Das      |
|       14 | delivered | Farhana Akter |
+----------+-----------+---------------+

The WHERE filters on a column of the other table: once joined, every column of both tables is available. AS is optional for table aliases (FROM orders o) in SQLite, MySQL and PostgreSQL; Oracle does not accept AS there at all, so many teams simply leave it out. Pick short, meaningful aliases (o for orders, c for customers), not a, b, c in random order.

Qualify your columns: the ambiguous column error

products and categories both have a column called name. Ask for name without saying which one, and the database refuses to guess:

SELECT name, price
FROM products
JOIN categories ON products.category_id = categories.category_id;

Error: ambiguous column name: name

MySQL and PostgreSQL say the same in their own words:

-- MySQL
SELECT name, price
FROM products
JOIN categories ON products.category_id = categories.category_id;

Error: ERROR 1052: Column 'name' in field list is ambiguous

-- PostgreSQL
SELECT name, price
FROM products
JOIN categories ON products.category_id = categories.category_id;

Error: column reference "name" is ambiguous

The fix is to qualify the column, and to give the two name columns different output names with column aliases:

SELECT p.name AS product, c.name AS category, p.price
FROM products p
JOIN categories c ON p.category_id = c.category_id
ORDER BY p.product_id;

+-----------------------------+-------------+-------+
| product                     | category    | price |
+-----------------------------+-------------+-------+
| Python Crash Course         | Books       |  1200 |
| Hands-On Machine Learning   | Books       |  2500 |
| Mechanical Keyboard         | Electronics |  4500 |
| Wireless Mouse              | Electronics |   900 |
| 27-inch Monitor             | Electronics | 18500 |
| SQL Masterclass             | Courses     |  3000 |
| AI Engineering Bootcamp     | Courses     | 15000 |
| Laptop Stand                | Accessories |  1500 |
| USB-C Hub                   | Accessories |  2200 |
| Noise-Cancelling Headphones | Electronics |  7500 |
+-----------------------------+-------------+-------+

price was not ambiguous (only products has it), so the query would run without p.. Qualify it anyway. If someone later adds a price column to categories, unqualified queries suddenly break — or, worse, a reader cannot tell which table a column comes from. In a join, qualify every column.

Where did the Gift Card go?

There are 11 products, but only 10 rows above. Count both:

SELECT COUNT(*) AS products FROM products;
SELECT COUNT(*) AS joined_rows
FROM products p
JOIN categories c ON p.category_id = c.category_id;

+----------+
| products |
+----------+
|       11 |
+----------+
+-------------+
| joined_rows |
+-------------+
|          10 |
+-------------+

Product 11 (Gift Card) has category_id NULL. NULL = 1 is not true (it is unknown, see BETWEEN, IN, LIKE and NULL), so the Gift Card matches no category and the inner join drops it. Category 5 (Furniture) has no products and is dropped too. Inner joins keep only the rows that match on both sides; when you need the unmatched rows, you need an outer join (next page). Comparing row counts before and after a join is the single best habit for catching this.

USING: when the column has the same name

When the join column has the same name in both tables, USING (column) is a shorter way to write the equality:

SELECT category_id, p.name AS product, c.name AS category
FROM products p
JOIN categories c USING (category_id)
WHERE category_id = 3
ORDER BY p.product_id;

+-------------+-------------------------+----------+
| category_id | product                 | category |
+-------------+-------------------------+----------+
|           3 | SQL Masterclass         | Courses  |
|           3 | AI Engineering Bootcamp | Courses  |
+-------------+-------------------------+----------+

USING (category_id) means ON p.category_id = c.category_id, and it merges the two columns into one, which is why category_id can be used here without a table prefix. SQLite, MySQL and PostgreSQL all support it; SQL Server does not. Use it when the names really match, as in this schema.

Avoid NATURAL JOIN. It joins on every column the two tables share by name. products NATURAL JOIN categories joins on category_id and name, and no product is called "Books", so it returns nothing — and it silently changes meaning whenever someone adds a column.

SELECT COUNT(*) AS natural_rows FROM products NATURAL JOIN categories;

+--------------+
| natural_rows |
+--------------+
|            0 |
+--------------+

Joins with GROUP BY: answering real questions

Everything from the earlier pages works on joined rows. How many orders has each customer placed, and when was the first one?

SELECT c.name, COUNT(*) AS orders, MIN(o.order_date) AS first_order
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY orders DESC, c.name;

+-----------------+--------+-------------+
| name            | orders | first_order |
+-----------------+--------+-------------+
| Farhana Akter   |      3 | 2026-02-14  |
| Nadia Rahman    |      3 | 2026-01-12  |
| Tanvir Ahmed    |      3 | 2026-01-20  |
| Imran Hossain   |      2 | 2026-04-02  |
| Mitu Das        |      1 | 2026-05-06  |
| Rafiq Islam     |      1 | 2026-02-25  |
| Sadia Chowdhury |      1 | 2026-03-15  |
+-----------------+--------+-------------+

• We group by c.customer_id as well as the name: two different customers can share a name, and the id keeps them apart.
• Seven customers, not eight. Karim has no orders, so the inner join removed him before GROUP BY ever saw him. For a churn model, the customers who never ordered are often the most interesting ones — the next page shows how to keep them.

The older comma syntax

You will meet joins written with a comma in FROM and the condition in WHERE. It means the same as an inner join: FROM orders o, customers c WHERE o.customer_id = c.customer_id.

Prefer JOIN … ON: it keeps "how tables connect" apart from "which rows I want", and the condition sits right next to the table it connects, so a missing one is easy to spot. (PostgreSQL rejects a JOIN with no ON; SQLite and MySQL quietly treat it as a cross join, which you will meet on the next page.) Forget the condition in the comma form and you get every pair — 14 orders × 8 customers:

SELECT COUNT(*) AS rows_without_condition
FROM orders, customers;

+------------------------+
| rows_without_condition |
+------------------------+
|                    112 |
+------------------------+

The same join in pandas

In Python for AI you combined DataFrames with merge. It is the same operation: how="inner" is an inner join, on= is USING. Let the database do the join when the data is in the database; it is usually faster and moves less data into Python.

import pandas as pd
from sqlhelp import con

joined = pd.read_sql("""
    SELECT o.order_id, o.customer_id, c.name, c.city
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    ORDER BY o.order_id
""", con)

orders = pd.read_sql("SELECT order_id, customer_id FROM orders ORDER BY order_id", con)
customers = pd.read_sql("SELECT customer_id, name, city FROM customers", con)
merged = orders.merge(customers, on="customer_id", how="inner")

print(len(joined), len(merged))
print(merged.equals(joined))
print(merged.head(3))

14 14
True
   order_id  customer_id          name        city
0         1            1  Nadia Rahman       Dhaka
1         2            2  Tanvir Ahmed  Chattogram
2         3            1  Nadia Rahman       Dhaka

Common mistakes

• Unqualified columns. SELECT name … over products and categories fails with "ambiguous column". Fix: p.name AS product, c.name AS category, and qualify every column in every join.
• Using the full table name after giving an alias. Once a table has an alias, the query must use the alias:
  SQLCopy

  SELECT orders.order_id FROM orders o LIMIT 1;

  OutputCopy

  Error: no such column: orders.order_id

  Fix: SELECT o.order_id FROM orders o.
• Joining on the wrong columns. This runs without any error and is completely wrong — it pairs order 8 with customer 8, who has never ordered:
  SQLCopy

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

  OutputCopy

  +----------+-----------------+
  | order_id | name            |
  +----------+-----------------+
  |        1 | Nadia Rahman    |
  |        2 | Tanvir Ahmed    |
  |        3 | Farhana Akter   |
  |        4 | Rafiq Islam     |
  |        5 | Sadia Chowdhury |
  |        6 | Imran Hossain   |
  |        7 | Mitu Das        |
  |        8 | Karim Uddin     |
  +----------+-----------------+

  Fix: join a foreign key to the primary key it references (o.customer_id = c.customer_id). The schema's REFERENCES clauses tell you which pairs are meant to be joined.
• Not noticing rows that disappeared. An inner join silently drops rows with no match or with NULL in the join column (the Gift Card, Karim). Fix: compare COUNT(*) before and after the join; if rows must be kept, use LEFT JOIN.
• Missing join condition. FROM orders, customers with no WHERE gives 112 rows of nonsense, and any SUM over it is 8 times too big. Fix: write JOIN … ON so the condition sits next to the table.

Try it yourself

1. Easy: List every payment with its order date: payment_id, order_date, amount, method, ordered by payment_id. (Join payments to orders.)
2. Medium: For each product that has been sold, show its name and the total quantity sold, biggest first (ties by name). Use order_items and products with aliases.
3. Hard: Using USING, show each category's name, its number of products and its average price rounded to 0 decimals, only for categories with at least 2 products, sorted by average price from high to low (ties by category name). Then explain in one sentence why Furniture and the Gift Card cannot appear.

Answers

-- 1
SELECT p.payment_id, o.order_date, p.amount, p.method
FROM payments p
JOIN orders o ON p.order_id = o.order_id
ORDER BY p.payment_id;

-- 2
SELECT pr.name, SUM(oi.quantity) AS total_quantity
FROM order_items oi
JOIN products pr ON oi.product_id = pr.product_id
GROUP BY pr.product_id, pr.name
ORDER BY total_quantity DESC, pr.name;

-- 3
SELECT c.name AS category, COUNT(*) AS products, ROUND(AVG(p.price), 0) AS avg_price
FROM products p
JOIN categories c USING (category_id)
GROUP BY c.name
HAVING COUNT(*) >= 2
ORDER BY avg_price DESC, category;

Furniture has no products and the Gift Card has no category, so neither has a partner on the other side, and an inner join keeps only matched pairs.

Summary

• A join rebuilds a combined view from tables linked by keys: usually a foreign key matched to the primary key it references.
• JOIN … ON condition (same as INNER JOIN) keeps every pair of rows for which the condition is true; a row may match many times or not at all.
• Unmatched rows, and rows with NULL in the join column, vanish from an inner join without warning — compare row counts.
• Use short table aliases and qualify every column; an unqualified shared column is an "ambiguous column" error in all three dialects.
• USING (col) is a short form when names match; avoid NATURAL JOIN.

Next: LEFT, RIGHT, FULL, CROSS and Self Joins keeps the rows an inner join throws away — customers who never ordered, products never sold — and shows joins that pair every row with every other row, or a table with itself.