SQL JOINs โ Combining Data from Multiple Tables
In real-world databases, information is rarely stored in one table.
For example: customers, orders, products, payments, employees, departments
A customer may exist in one table while their orders exist in another. JOINs allow us to combine related data from multiple tables. This is one of the most important SQL concepts for a Data Analyst.
๐ง 1. Why Do We Need JOINs?
Suppose we have two tables:
customers
customer_id | customer_name
101 | Alice
102 | Bob
103 | Charlie
orders
order_id | customer_id | amount
1 | 101 | 500
2 | 101 | 800
3 | 102 | 300
The customer name is stored in "customers". The order amount is stored in "orders".
To answer:
ยซHow much did each customer spend?ยป
We need to combine the tables. That's where JOIN comes in.
๐ 2. Basic JOIN Structure
SELECT
c.customer_name,
o.order_id,
o.amount
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;
Here:
customers โ c, orders โ o. These are called table aliases.The condition:
ON c.customer_id = o.customer_id tells SQL how the tables are related.๐ 3. The JOIN Key
A JOIN usually connects tables through a related column.
customers.customer_id โ orders.customer_id
Often: one table contains a primary key, another table contains the corresponding foreign key.
Example:
customers.customer_id โ Primary Key, orders.customer_id โ Foreign Key.๐งฉ 4. INNER JOIN
"INNER JOIN" returns only rows that have a match in both tables.
SELECT c.customer_name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
Result:
Alice | 1 | 500
Alice | 2 | 800
Bob | 3 | 300
Charlie is missing because Charlie has no matching order.
Customers โฉ Orders - Only matching records.
๐ 5. LEFT JOIN
"LEFT JOIN" returns: All rows from the left table + matching rows from the right table.
SELECT c.customer_name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
Result includes:
Charlie | NULL | NULL
๐ฏ 6. Finding Customers Who Never Ordered
This is a very common interview and analytics problem.
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 technique is often called an anti-join pattern.
๐ 7. RIGHT JOIN
"RIGHT JOIN" returns: All rows from the right table + matching rows from the left table.
In practice, many analysts prefer rewriting a RIGHT JOIN as a LEFT JOIN by switching table order because it is often easier to read.
๐ 8. FULL OUTER JOIN
"FULL OUTER JOIN" returns: All rows from both tables, whether they match or not.
Conceptually:
LEFT JOIN + RIGHT JOIN
It can reveal: matching records, customers without orders, orders without matching customers.
โ ๏ธ Not every database supports "FULL OUTER JOIN" directly.
๐ 9. INNER JOIN vs LEFT JOIN
โข INNER JOIN = Returns only customers with matching orders.
โข LEFT JOIN = Returns all customers, including those without orders.
Simple rule:
INNER JOIN = matching records,
LEFT JOIN = keep everything from the left table.
๐ 10. JOIN + Aggregation
Question:
ยซHow much has each customer spent?ยป