SQL JOIN with Three Tables

---

When working with relational databases, data is often stored in separate tables to maintain normalization. However, to extract meaningful information, you might need to combine data from multiple tables. SQL JOIN operations are essential for this purpose, and they allow you to retrieve related data from multiple tables in a single query.

---

1. Understanding SQL JOIN Basics

Before diving into examples of joining three tables, let's quickly review how SQL JOINs work.

SQL JOIN operations combine rows from two or more tables based on a related column. The most commonly used types of JOINs are:

• INNER JOIN: Returns rows when there is a match in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.
• RIGHT JOIN (RIGHT OUTER JOIN): Similar to LEFT JOIN, but returns all rows from the right table and matched rows from the left table.
• FULL OUTER JOIN: Returns rows when there is a match in one of the tables. If there is no match, NULL values are returned for missing matches.

For this example, we will work with three tables:

• Customers
• Orders
• Products

---

2. Sample Database Schema

Here’s a brief overview of the three tables we'll use in our examples:

Customers Table

customer_id	name	email
1	John Doe	john@example.com
2	Jane Smith	jane@example.com
3	Bob Johnson	bob@example.com

Orders Table

order_id	customer_id	product_id	order_date
1001	1	101	2024-12-01
1002	2	102	2024-12-05
1003	1	103	2024-12-07

Products Table

product_id	product_name	price
101	Laptop	1000
102	Smartphone	500
103	Tablet	300

---

3. Using SQL JOIN to Combine Three Tables

3.1 INNER JOIN: Retrieving Customers and Their Orders with Product Details

The INNER JOIN returns records that have matching values in both tables. If a customer has placed an order, the query will return their information along with the product details.

Query:

SELECT 
    customers.name, 
    orders.order_id, 
    products.product_name, 
    products.price
FROM 
    customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN products ON orders.product_id = products.product_id;

Explanation:

• We join the customers table with the orders table on the customer_id column.
• Then, we join the orders table with the products table on the product_id column.
• This returns the names of customers, their order IDs, product names, and product prices.

Result:

name	order_id	product_name	price
John Doe	1001	Laptop	1000
Jane Smith	1002	Smartphone	500
John Doe	1003	Tablet	300

---

3.2 LEFT JOIN: Getting All Customers and Their Orders (Including Those Without Orders)

The LEFT JOIN ensures all customers are included, even if they haven't placed any orders. For customers who haven't ordered anything, the product details will be NULL.

Query:

SELECT 
    customers.name, 
    orders.order_id, 
    products.product_name, 
    products.price
FROM 
    customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN products ON orders.product_id = products.product_id;

Explanation:

• In a RIGHT JOIN, we first join customers and orders, ensuring that all orders are included, even if there’s no matching customer.
• The second RIGHT JOIN ensures that all products are included, even if no order has been placed for them.

Result:

name	order_id	product_name	price
John Doe	1001	Laptop	1000
Jane Smith	1002	Smartphone	500
John Doe	1003	Tablet	300

---

3.4 FULL OUTER JOIN: Combining All Data from Three Tables

A FULL OUTER JOIN combines the results of both LEFT JOIN and RIGHT JOIN. It returns all rows when there is a match in one of the tables. If there’s no match, NULL values are returned for missing matches.

Query:

SELECT 
    customers.name, 
    orders.order_id, 
    products.product_name, 
    products.price
FROM 
    customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id
FULL OUTER JOIN products ON orders.product_id = products.product_id;

Explanation:

• A FULL OUTER JOIN ensures that every customer, order, and product are included in the result set, even if there’s no corresponding match for a record.

Result:

name	order_id	product_name	price
John Doe	1001	Laptop	1000
Jane Smith	1002	Smartphone	500
John Doe	1003	Tablet	300
Bob Johnson	NULL	NULL	NULL

---

4. Best Practices for Joining Three Tables

• Use Aliases for Simplicity: When dealing with multiple tables, using table aliases (shortened names) can make your query easier to read.

  Example:

  SELECT 
      c.name, 
      o.order_id, 
      p.product_name, 
      p.price
  FROM 
      customers c
  INNER JOIN orders o ON c.customer_id = o.customer_id
  INNER JOIN products p ON o.product_id = p.product_id;
  

• Ensure Proper Relationships: Make sure your tables are properly related through foreign keys (e.g., customer_id in orders table refers to customers table). This ensures the correctness of your JOIN results.

• Limit Your Results: If you expect a large result set, always use LIMIT or TOP to reduce the size of the data returned.

---

---