🚀 Data Analyst Roadmap — Part 14
🗄️ SQL — Level 4: JOINs
One of the most important SQL skills for a Data Analyst is understanding JOINs.
In real-world databases, information is rarely stored in one giant table. Instead, data is usually split across multiple related tables.
For example:
Customers → Orders → Products → Payments
JOINs allow you to bring related information together.
1️⃣ What Is a JOIN?
A JOIN combines rows from two or more tables using a related column.
Suppose you have:
Customers
Customer_ID | Customer_Name | City 101 | John | Pune 102 | Sarah | Mumbai 103 | Mike | Delhi
Orders
Order_ID | Customer_ID | Sales 5001 | 101 | 50,000 5002 | 102 | 70,000 5003 | 101 | 30,000
Both tables have Customer_ID. That common field allows us to connect them.
2️⃣ Why Are JOINs Important?
Imagine your manager asks: "Show me each customer's name along with their total sales."
The customer name is in Customers. The sales amount is in Orders. You need to combine the tables. That's a JOIN problem.
3️⃣ Basic JOIN Syntax
SELECT Customers.Customer_Name, Orders.Sales FROM Customers JOIN Orders ON Customers.Customer_ID = Orders.Customer_ID;
The ON condition tells SQL: How are these two tables related?
4️⃣ INNER JOIN
INNER JOIN returns only records where a match exists in both tables.
If Customer 104 exists only in Orders, it won't be returned.
Result after INNER JOIN:
John | 50,000
Sarah | 70,000
5️⃣ INNER JOIN — Simple Rule
INNER JOIN = Only matching records
Think: Table A ∩ Table B
6️⃣ LEFT JOIN
LEFT JOIN returns All rows from the left table plus matching rows from the right table.
Query:
SELECT Customers.Customer_Name, Orders.Sales FROM Customers LEFT JOIN Orders ON Customers.Customer_ID = Orders.Customer_ID;
Result:
John | 50,000
Sarah | 70,000
Mike | NULL
Mike doesn't have an order, but because Customers is the left table, Mike remains in the result.
7️⃣ Why LEFT JOIN Is Extremely Important
To find customers who have never placed an order:
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;
This is a very common analytical pattern.
8️⃣ RIGHT JOIN
RIGHT JOIN is the reverse of LEFT JOIN. It returns All rows from the right table plus matching rows from the left.
9️⃣ Do Data Analysts Need RIGHT JOIN?
You should understand it. However, many analysts prefer rewriting a RIGHT JOIN as a LEFT JOIN because LEFT JOIN is often easier to read.
A RIGHT JOIN B can be rewritten as B LEFT JOIN A.
🔟 FULL OUTER JOIN
A FULL OUTER JOIN returns:
• Matching rows
• Unmatched rows from the left
• Unmatched rows from the right
1️⃣1️⃣ FULL OUTER JOIN Example
SELECT c.Customer_ID, c.Customer_Name, o.Order_ID FROM Customers c FULL OUTER JOIN Orders o ON c.Customer_ID = o.Customer_ID;
Useful for identifying data inconsistencies and missing relationships.
1️⃣2️⃣ JOIN Comparison
• INNER JOIN: Matching rows only
• LEFT JOIN: All left + matching right
• RIGHT JOIN: All right + matching left
• FULL OUTER JOIN: Everything from both
Most important for Data Analysts: INNER JOIN and LEFT JOIN. Master these first.
1️⃣3️⃣ JOIN with Multiple Columns
ON A.Product_ID = B.Product_ID AND A.Region = B.Region
Composite join conditions are common in real-world datasets.
1️⃣4️⃣ Joining More Than Two Tables