Table Aliases and INNER JOIN

Objectives

By the end of this lesson, you should be able to:

• Give a table a short alias with AS
• Combine rows from two tables with INNER JOIN
• Explain which rows an INNER JOIN keeps and which it drops

💡 Why this matters: Module 9 built the relationships, customers connected to orders through a foreign key. JOIN is how a query actually combines matching rows from both tables into a single result, this is the query-side counterpart to everything Module 9 set up.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Setup

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date DATE NOT NULL,
    amount NUMERIC(10,2) NOT NULL
);

INSERT INTO customers (name) VALUES ('Riley Nguyen'), ('Casey Brooks'), ('Morgan Alvarez');
INSERT INTO orders (customer_id, order_date, amount) VALUES
  (1, '2024-01-10', 120.00),
  (1, '2024-02-15', 45.00),
  (2, '2024-01-20', 75.00);

Morgan Alvarez has no orders yet, this matters later in this lesson.

Table Aliases

SELECT c.name, o.order_date, o.amount
FROM customers c INNER JOIN orders o ON c.id = o.customer_id
ORDER BY c.name, o.order_date;

     name       | order_date | amount
-----------------+------------+---------
 Casey Brooks   | 2024-01-20 |  75.00
 Riley Nguyen   | 2024-01-10 | 120.00
 Riley Nguyen   | 2024-02-15 |  45.00

customers c and orders o give each table a short alias, c and o, right after its name (AS is optional here and usually omitted). Once tables are joined, both might have similarly-named columns, or the full names simply get repetitive, aliases keep every reference in the query (c.name, o.order_date) short and unambiguous about which table it comes from.

INNER JOIN

INNER JOIN is the join used above. It matches rows from both tables using the condition after ON (c.id = o.customer_id), and keeps only rows where a match exists on both sides. Morgan Alvarez doesn’t appear anywhere in this result, she has no matching row in orders, so INNER JOIN drops her entirely. This is the defining trait of INNER JOIN: unmatched rows, on either side, don’t make it into the result.

Plain JOIN (without INNER in front) means the exact same thing, INNER is the default and is often left out.

Reading a JOIN

FROM customers c INNER JOIN orders o ON c.id = o.customer_id reads naturally left to right: start with customers, join in orders, matching wherever a customer’s id equals an order’s customer_id. The SELECT list can then freely mix columns from both tables, as if they were one combined table for the rows that matched.

Try It

1. Write a query joining customers and orders with INNER JOIN, showing name, order_date, and amount, and confirm Morgan Alvarez doesn’t appear.
2. Add a WHERE clause to the same query, keeping only orders with amount greater than 50.
3. Rewrite the join using full table names instead of aliases (no c or o), and compare readability.
4. Explain, in your own words, why INNER JOIN is sometimes described as “the intersection” of two tables.

Recap

• A table alias, written right after the table name, shortens references to it throughout the query.
• INNER JOIN table2 ON condition combines matching rows from both tables, keeping only rows with a match on both sides.
• Plain JOIN means the same thing as INNER JOIN.
• Rows with no match on either side are dropped entirely, this is what distinguishes INNER JOIN from the join types in the next lesson.

Next lesson: LEFT JOIN and RIGHT JOIN, keeping unmatched rows from one side instead of dropping them.