MySQL Joins

Combine data from multiple tables

🔗 What are Joins?

Joins combine rows from two or more tables based on related columns. They're essential for retrieving data spread across multiple tables, enabling you to create meaningful relationships and comprehensive result sets from your database. 


-- Basic join example
SELECT customers.name, orders.order_date
FROM customers
JOIN orders ON customers.id = orders.customer_id;

Output:

name order_date
John Doe 2024-01-15
Jane Smith 2024-01-20

Types of Joins

🔵

INNER JOIN

Returns matching rows from both tables 

SELECT * FROM a INNER JOIN b ON a.id = b.id
◀️

LEFT JOIN

Returns all rows from left table 

SELECT * FROM a LEFT JOIN b ON a.id = b.id
▶️

RIGHT JOIN

Returns all rows from right table 

SELECT * FROM a RIGHT JOIN b ON a.id = b.id
⬜

FULL OUTER JOIN

Returns all rows from both tables 

SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id

🔹 Understanding Join Syntax

The basic join syntax consists of selecting columns from multiple tables and specifying how they relate. The ON clause defines the relationship between tables, typically matching primary keys with foreign keys. Understanding this syntax is fundamental to working with relational databases and retrieving connected data efficiently. 

-- Basic join structure
SELECT 
    table1.column1,
    table2.column2
FROM table1
JOIN table2 ON table1.common_field = table2.common_field;

Join Components:

  • SELECT: Specify columns from both tables
  • FROM: The first (left) table
  • JOIN: The second (right) table
  • ON: The condition that links the tables

🔹 Simple Join Example

Let's look at a practical example joining customers with their orders. This demonstrates how joins retrieve related information from separate tables. The customer table stores customer details, while the orders table tracks purchases. By joining them, we can see which customer made which order in a single result set. 

-- Join customers with their orders
SELECT 
    c.customer_id,
    c.name AS customer_name,
    o.order_id,
    o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Output:

customer_id customer_name order_id total_amount
1 Alice Brown 101 250.00
2 Bob Wilson 102 180.50

🔹 Joining Multiple Tables

You can join more than two tables in a single query by chaining multiple JOIN clauses. This is useful when data is normalized across several tables. Each join adds another table to the result set, allowing you to combine information from various sources. The key is maintaining clear relationships through proper ON conditions. 

-- Join three tables: customers, orders, and products
SELECT 
    c.name AS customer,
    o.order_date,
    p.product_name,
    p.price
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN products p ON o.product_id = p.product_id;

Output:

customer order_date product_name price
Alice Brown 2024-01-15 Laptop 999.99
Bob Wilson 2024-01-20 Mouse 29.99

🔹 Join with WHERE Clause

Combining joins with WHERE clauses allows you to filter the joined results based on specific conditions. This gives you precise control over which rows appear in your final result set. You can filter on columns from any of the joined tables, making your queries both powerful and flexible for complex data retrieval scenarios. 

-- Join with filtering
SELECT 
    c.name,
    o.order_date,
    o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > 100
ORDER BY o.total_amount DESC;

Output:

name order_date total_amount
Alice Brown 2024-01-15 250.00
Bob Wilson 2024-01-20 180.50

🧠 Test Your Knowledge

What does the ON clause do in a JOIN?