Difficulty: Advanced
Joins are the mechanism by which SQL combines rows from two or more tables based on a related column, and they are fundamental to working with normalized relational databases. In real-world analytics, data is almost never stored in a single table -- customer information, orders, products, and transactions are typically split across multiple tables for efficiency and data integrity. Understanding joins is therefore essential for any data analyst.
An INNER JOIN returns only the rows where there is a match in both tables. If a customer has no orders, they will not appear in the result of an inner join between customers and orders. A LEFT JOIN returns all rows from the left table and matching rows from the right table, filling in NULL where there is no match. This is crucial for analytics scenarios like finding customers who have never placed an order. A RIGHT JOIN is the mirror image, keeping all rows from the right table.
Subqueries (also called nested queries) are SELECT statements embedded within another SQL statement. They can appear in the WHERE clause (for filtering based on aggregated or computed values), in the FROM clause (as derived tables), or in the SELECT clause (as scalar subqueries). The EXISTS operator checks whether a subquery returns any rows, which is often more efficient than IN for large datasets.
Understanding the performance implications of different join types and subquery patterns is important for working with large datasets. INNER JOINs are generally the fastest because they produce the smallest result sets. Correlated subqueries (which reference the outer query) execute once per row and can be slow, while non-correlated subqueries execute once and are cached. In many cases, joins can replace subqueries with better performance.
# Simulating tables
customers = [
{'id': 1, 'name': 'Alice'},
{'id': 2, 'name': 'Bob'},
{'id': 3, 'name': 'Charlie'},
{'id': 4, 'name': 'Diana'},
]
orders = [
{'order_id': 101, 'customer_id': 1, 'amount': 250},
{'order_id': 102, 'customer_id': 1, 'amount': 150},
{'order_id': 103, 'customer_id': 2, 'amount': 300},
{'order_id': 104, 'customer_id': 5, 'amount': 100}, # customer_id 5 doesn't exist
]
# SQL: SELECT c.name, o.order_id, o.amount
# FROM customers c INNER JOIN orders o ON c.id = o.customer_id
print("SQL: SELECT c.name, o.order_id, o.amount")
print(" FROM customers c INNER JOIN orders o ON c.id = o.customer_id")
print()
print("INNER JOIN Result:")
cust_map = {c['id']: c for c in customers}
for o in orders:
if o['customer_id'] in cust_map:
c = cust_map[o['customer_id']]
print(f" {c['name']}: Order #{o['order_id']}, ${o['amount']}")
print(f"\nNote: Diana (no orders) and Order #104 (no matching customer) excluded")INNER JOIN only includes rows where the join key exists in both tables. Diana (customer with no orders) and Order #104 (order with no matching customer) are both excluded. This is the most common join type and produces the intersection of matched rows.
customers = [
{'id': 1, 'name': 'Alice'},
{'id': 2, 'name': 'Bob'},
{'id': 3, 'name': 'Charlie'},
{'id': 4, 'name': 'Diana'},
]
orders = [
{'order_id': 101, 'customer_id': 1, 'amount': 250},
{'order_id': 102, 'customer_id': 1, 'amount': 150},
{'order_id': 103, 'customer_id': 2, 'amount': 300},
]
# SQL: SELECT c.name, o.order_id, o.amount
# FROM customers c LEFT JOIN orders o ON c.id = o.customer_id
print("LEFT JOIN Result:")
# Build order lookup
order_map = {}
for o in orders:
order_map.setdefault(o['customer_id'], []).append(o)
for c in customers:
cid = c['id']
if cid in order_map:
for o in order_map[cid]:
print(f" {c['name']}: Order #{o['order_id']}, ${o['amount']}")
else:
print(f" {c['name']}: NULL (no orders)")
print()
# Common analytics pattern: find customers with NO orders
# SQL: SELECT name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.order_id IS NULL
print("Customers with no orders:")
order_cust_ids = {o['customer_id'] for o in orders}
for c in customers:
if c['id'] not in order_cust_ids:
print(f" {c['name']}")LEFT JOIN keeps all rows from the left table (customers) even when there is no match in the right table (orders). Unmatched rows get NULL for the right table columns. The pattern of LEFT JOIN + WHERE right.key IS NULL is the standard way to find records with no corresponding match.
# Simulating subquery patterns with Python
employees = [
{'name': 'Alice', 'dept': 'Engineering', 'salary': 95000},
{'name': 'Bob', 'dept': 'Marketing', 'salary': 72000},
{'name': 'Charlie', 'dept': 'Engineering', 'salary': 105000},
{'name': 'Diana', 'dept': 'HR', 'salary': 68000},
{'name': 'Eve', 'dept': 'Marketing', 'salary': 78000},
{'name': 'Frank', 'dept': 'Engineering', 'salary': 88000},
]
# Subquery 1: WHERE salary > (SELECT AVG(salary) FROM employees)
avg_salary = sum(e['salary'] for e in employees) / len(employees)
print(f"Average salary: ${avg_salary:,.0f}")
print("\nEmployees above average (subquery pattern):")
for e in employees:
if e['salary'] > avg_salary:
print(f" {e['name']}: ${e['salary']:,}")
print()
# Subquery 2: WHERE dept IN (SELECT dept FROM employees GROUP BY dept HAVING COUNT(*) > 1)
dept_counts = {}
for e in employees:
dept_counts[e['dept']] = dept_counts.get(e['dept'], 0) + 1
multi_depts = {d for d, c in dept_counts.items() if c > 1}
print(f"Departments with >1 employee: {sorted(multi_depts)}")
print("Employees in those departments:")
for e in employees:
if e['dept'] in multi_depts:
print(f" {e['name']} ({e['dept']})")Subquery pattern 1 filters employees whose salary exceeds the overall average, which requires computing the average first (the subquery) then using it as a filter. Pattern 2 uses a subquery with GROUP BY and HAVING to find departments meeting a condition, then filters employees by those departments.
# Simulating EXISTS vs IN with Python
customers = [
{'id': 1, 'name': 'Alice', 'city': 'NYC'},
{'id': 2, 'name': 'Bob', 'city': 'LA'},
{'id': 3, 'name': 'Charlie', 'city': 'NYC'},
{'id': 4, 'name': 'Diana', 'city': 'Chicago'},
]
orders = [
{'customer_id': 1, 'product': 'Laptop'},
{'customer_id': 1, 'product': 'Mouse'},
{'customer_id': 3, 'product': 'Keyboard'},
]
# SQL with IN:
# SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders)
print("Using IN pattern:")
order_cust_ids = {o['customer_id'] for o in orders}
for c in customers:
if c['id'] in order_cust_ids:
print(f" {c['name']} has orders")
print()
# SQL with EXISTS:
# SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
print("Using EXISTS pattern:")
for c in customers:
exists = any(o['customer_id'] == c['id'] for o in orders)
if exists:
print(f" {c['name']} has orders")
print()
# NOT EXISTS: customers without orders
print("NOT EXISTS (no orders):")
for c in customers:
not_exists = not any(o['customer_id'] == c['id'] for o in orders)
if not_exists:
print(f" {c['name']}")IN collects all matching IDs from the subquery into a set, then checks membership. EXISTS checks row-by-row whether the subquery returns any result. Both give the same answer, but EXISTS is often faster with large datasets because it short-circuits after finding the first match. NOT EXISTS is the standard pattern for anti-joins.
INNER JOIN, LEFT JOIN, RIGHT JOIN, subqueries, EXISTS