Combining Tables with JOINs

Our marketplace schema stores related information across several tables. Customers place orders, orders contain products through order items, and products belong to categories and sellers. To answer most real-world questions you must combine these tables. That is exactly what SQL joins do.

Learning Goals

• Understand INNER, LEFT, and (conceptual) RIGHT/FULL joins using marketplace data
• Write join queries that combine customers, orders, products, categories, and sellers
• Use table aliases and fully qualified column names to avoid ambiguity
• Recognize common mistakes when joining multiple tables

INNER JOIN

An INNER JOIN returns only rows where the join condition matches. Use it when you need data that exists on both sides - for example, listing customers who have selected a preferred category.

Customers with preferred categories

SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       cat.category_name
FROM customer AS c
INNER JOIN category AS cat ON cat.category_id = c.preferred_category_id
ORDER BY customer_name;

Customers whose preferred_category_id is NULL are excluded because there is no matching category row.

LEFT JOIN

A LEFT JOIN returns every row from the left table and matches from the right table when they exist. This is perfect for finding customers without a stated preference.

All customers, flagging missing preferences

SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       cat.category_name
FROM customer AS c
LEFT JOIN category AS cat ON cat.category_id = c.preferred_category_id
ORDER BY customer_name;

Customers without a preferred category show NULL in category_name, making them easy to identify.

RIGHT and FULL JOIN (Conceptual)

MySQL supports RIGHT JOIN but does not implement FULL JOIN, so it is still important to understand both:

• RIGHT JOIN: returns every row from the right table plus matches from the left. You can usually rewrite this as a LEFT JOIN by swapping table order.
• FULL JOIN: returns every row from both tables, matching where possible and showing NULL when no match exists. You can emulate this by combining a LEFT JOIN and a RIGHT JOIN with a UNION.

Joining More Than Two Tables

Most questions touch several tables. For example, to see which customers ordered which products - and which category each product belongs to - you need multiple joins.

Order item details across four tables

SELECT oi.order_item_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       p.sku,
       p.product_name,
       cat.category_name,
       o.order_date,
       o.status
FROM order_item AS oi
JOIN orders   AS o   ON o.order_id = oi.order_id
JOIN customer AS c   ON c.customer_id = o.customer_id
JOIN product  AS p   ON p.product_id = oi.product_id
JOIN category AS cat ON cat.category_id = p.category_id
ORDER BY o.order_date DESC, o.order_id;

You can chain as many joins as needed. Just add one join clause per relationship.

Joining with Sellers

Because each product can reference a seller, you can extend the previous example to include store information.

Products with seller detail

SELECT p.sku,
       p.product_name,
       cat.category_name,
       s.store_name,
       CONCAT(s.first_name, ' ', s.last_name) AS seller_contact
FROM product AS p
JOIN category AS cat ON cat.category_id = p.category_id
LEFT JOIN seller AS s ON s.seller_id = p.seller_id
ORDER BY cat.category_name, p.sku;

The LEFT JOIN ensures products still appear even if a seller has not been assigned yet.

Using Table Aliases

Aliases keep join queries readable and help you disambiguate similarly named columns.

Aliases clarify complex joins

SELECT c.customer_id,
       c.first_name,
       c.last_name,
       p.sku,
       o.order_date
FROM customer AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_item AS oi ON oi.order_id = o.order_id
JOIN product AS p ON p.product_id = oi.product_id
WHERE o.status = 'Delivered';

The alias (AS c, AS o, AS oi, AS p) lets you refer to columns quickly without repeating full table names.

Handling Ambiguous Column Names

When two tables share a column name (for example, both customer and seller have first_name), qualify the column with its alias to avoid errors.

Fully qualify duplicate column names

SELECT o.order_id,
       o.status AS order_status,
       t.status AS transaction_status
FROM orders AS o
JOIN transactions AS t ON t.order_id = o.order_id
ORDER BY o.order_id;

Common Pitfalls

• Missing join condition: Forgetting the ON clause produces a Cartesian product (every row matched with every other row).
• Wrong join type: Using INNER JOIN when you really need LEFT JOIN drops unmatched rows.
• Ambiguous columns: Always prefix duplicated column names with their table alias.
• Join order confusion: Sketch the relationships (or refer to the ER diagram) before writing complicated join chains.
• Null handling: Be aware of optional foreign keys such as customer.preferred_category_id and product.seller_id.

Summary

• Use INNER JOIN when you only care about matching rows.
• Use LEFT JOIN to keep unmatched left-side records (great for spotting null foreign keys).
• Chain joins to pull together customers, orders, products, categories, and sellers.
• Rely on aliases and qualified column names for clarity and correctness.

Quiz

Show quiz

1. Which join ensures every customer appears, even if they have not set a preferred category?
   A) INNER JOIN
   B) LEFT JOIN
   C) RIGHT JOIN
   D) FULL JOIN

2. What columns do you join on when connecting orders to customer?
   A) order_id = customer_id
   B) customer_id in both tables
   C) product_id in both tables
   D) category_id in both tables

3. Why is product joined to category in many examples?
   A) They share the same primary key
   B) Each product belongs to a category via category_id
   C) Every category has exactly one product
   D) To filter out products with NULL seller assignments

4. True or False: You can rewrite any RIGHT JOIN as a LEFT JOIN by swapping table order.
   A) True
   B) False

---

Answers

1. B
2. B
3. B
4. A