SELECT c.Customer_Name, o.Order_ID, p.Product_Name, o.Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
JOIN Products p ON o.Product_ID = p.Product_ID;

1️⃣5️⃣ Table Aliases

Customers c, Orders o, Products p

Instead of Customers.Customer_ID, you can write c.Customer_ID. Much easier to read.

1️⃣6️⃣ JOIN + GROUP BY

This is one of the most important patterns.

SELECT c.Customer_Name, SUM(o.Sales) AS Total_Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
GROUP BY c.Customer_Name;

JOIN + SUM + GROUP BY is a very common interview question.

1️⃣7️⃣ JOIN + WHERE

SELECT c.Customer_Name, o.Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE c.Department = 'IT';

JOIN connects, WHERE filters.

1️⃣8️⃣ JOIN + HAVING

SELECT c.Customer_Name, SUM(o.Sales) AS Total_Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
GROUP BY c.Customer_Name
HAVING SUM(o.Sales) > 100000;

Pattern: JOIN → GROUP BY → SUM → HAVING

1️⃣9️⃣ The Most Important LEFT JOIN Pattern

SELECT c.Customer_ID, c.Customer_Name
FROM Customers c
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE o.Customer_ID IS NULL;

Answers: Which customers have no orders?

General pattern: LEFT JOIN + WHERE right_table.key IS NULL

2️⃣0️⃣ JOIN and NULL

After a LEFT JOIN, unmatched columns become NULL. That NULL tells us: No matching order was found.

2️⃣1️⃣ Self JOIN

A Self JOIN joins a table to itself. Useful for employee-manager relationships.

SELECT e.Employee_Name AS Employee, m.Employee_Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.Manager_ID = m.Employee_ID;

2️⃣2️⃣ Many-to-One Relationships

Customers → Orders is One-to-Many. From Orders perspective, it's Many-to-One. Understanding direction is critical.

2️⃣3️⃣ The Duplicate Row Problem

If John has 3 orders, after JOIN John appears 3 times. That's correct, not an error. Always understand relationship cardinality.

2️⃣4️⃣ JOIN Multiplication

If a customer has 3 orders and 4 payments, an incorrect join can produce 12 combinations and inflate Sales, Counts, Profit. Always check grain before aggregating.

2️⃣5️⃣ What Is Table Grain?

Grain means: What does one row represent?

Customers: One row = one customer

Orders: One row = one order

2️⃣6️⃣ JOIN vs UNION

• JOIN: Combines tables horizontally. Adds columns.

• UNION: Combines results vertically. Adds rows.

2️⃣7️⃣ Practical Business Example

Question: What is total sales by region?

SELECT c.Region, SUM(o.Sales) AS Total_Sales
FROM Customers c
JOIN Orders o ON c.Customer_ID = o.Customer_ID
GROUP BY c.Region
ORDER BY Total_Sales DESC;

🧪 Practical Interview Challenge

Q1. Show customer names with their orders.

SELECT c.Customer_Name, o.Order_ID, o.Sales
FROM Customers c 
JOIN Orders o ON c.Customer_ID = o.Customer_ID;

Q2. Show all customers, including those without orders.

SELECT c.Customer_Name, o.Order_ID, o.Sales
FROM Customers c 
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID;

Q3. Find customers who have never ordered.

SELECT c.Customer_ID, c.Customer_Name
FROM Customers c 
LEFT JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE o.Customer_ID IS NULL;

Q4. Find total sales per customer.

SELECT c.Customer_Name, SUM(o.Sales) AS Total_Sales
FROM Customers c 
JOIN Orders o ON c.Customer_ID = o.Customer_ID
GROUP BY c.Customer_Name;