Day 35: Joins in MySQL - Combining Data From Multiple Tables!

Today, let us explore Joins in MySQL, which allow us to combine data from multiple tables based on a related column!

?? What are SQL Joins?

A JOIN is used to retrieve data from two or more tables based on a common column

Types of Joins in MySQL:

? INNER JOIN - Returns only matching records

? LEFT JOIN - Returns all records from the left table and matching records from the right

? RIGHT JOIN - Returns all records from the right table and matching records from the left

? FULL JOIN (Not in MySQL, but can be emulated) - Returns all records from both tables

? CROSS JOIN - Returns the Cartesian product (all possible combinations)

1. INNER JOIN - Matching Records from Both Tables

Returns only rows where there is a match in both tables

Example: Getting Orders with Customer Details

SELECT customers.customer_id, customers.name, orders.order_id, orders.amount 
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id        

Returns only customers who placed orders

2. LEFT JOIN - All from the Left Table, Matching from the Right

Returns all rows from the left table and matching rows from the right table. If no match, NULL is returned

Example: Fetching All Customers and their Orders (Even if No Order Exists)

SELECT customers.customer_id, customers.name, orders.order_id, orders.amount
FROM customers
LEFT JOIN ON customers.customer_id = orders.customer_id;        

Returns all customers, including those who haven't placed orders

3. RIGHT JOIN - All from the Right Table, Matching from the Left

Returns all rows from the right table and matching rows from the left table. If no match, NULL is returned

Example: Fetching All Orders and their Customers (Including Orders without Customer Data)

SELECT customers.customer_id, customers.name, orders.order_id, orders.amount
FROM customers
RIGHT JOIN ON customers.customer_id = orders.customer_id;        

Returns all orders, even those without a valid customer record

4. FULL JOIN – All from Both Tables (Emulated in MySQL)

MySQL doesn't support FULL JOIN, but it can be simulated using UNION

Example: Fetching All Customers and All Orders

SELECT customers.customer_id, customers.name, orders.order_id, orders.amount
FROM customers 
LEFT JOIN ON customers.customer_id = orders.customer_id
UNION
SELECT customers.customer_id, customers.name, orders.order_id, orders.amount
FROM customers 
RIGHT JOIN ON customers.customer_id = orders.customer_id;        

Returns all customers and all orders, even if there's no match

5. CROSS JOIN – All Possible Combinations

Returns every possible combination of rows from both tables (Cartesian product)

Example: Assigning All Products to All Customers (Not a Practical Use Case)

SELECT customers.name, products.product_name
FROM customers
CROSS JOIN products;        

Every customer is paired with every product

? Choosing the Right JOIN

?? Use INNER JOIN when you only need matching data

?? Use LEFT JOIN when you need all from the left table, even if no match

?? Use RIGHT JOIN when you need all from the right table, even if no match

?? Use FULL JOIN (via UNION) when you need everything from both

#100DaysOfCode #MySQL #SQL #Database #DataAnalysis #Learning #Python #BackendDevelopment