Combining Data from Multiple Tables
Real databases spread data across many tables to avoid duplication. JOINs let you combine related tables back together for analysis. Understanding JOINs is essential for working with any production database.
JOIN Types
- INNER JOIN — only matching rows from both tables
- LEFT JOIN — all rows from the left table, matching rows from the right
- RIGHT JOIN — all rows from the right table, matching rows from the left
- FULL OUTER JOIN — all rows from both tables, with NULLs where no match
Subqueries
A subquery is a query nested inside another query. Use subqueries when you need to filter based on an aggregated value (e.g., employees earning above the average salary) or when the logic is clearer as a step-by-step computation.
Rule of thumb: use JOINs when combining columns from different tables. Use subqueries when filtering based on computed values.
Key Takeaway
JOINs combine tables horizontally. Subqueries compute intermediate results. Both are essential for real-world data extraction.