SQL Fundamentals
SQL-SQL-JOIN-Guide
Master the art of combining data from multiple tables using SQL JOINs. Learn different types of JOINs and when to use them effectively.
You'll Learn
- Different types of JOINs
- INNER JOIN vs OUTER JOIN
- Multiple table joins
- JOIN performance optimization
Free Beta Access
The Most Beautiful Database IDE You've Been Looking For
Get early access to our beta version and help us shape the future of SQL learning.
1. Sample Tables
Let's work with two related tables: employees and departments
employees table
id | first_name | last_name | dept_id | salary ----+-----------+-----------+---------+-------- 1 | John | Doe | 1 | 60000 2 | Jane | Smith | 2 | 65000 3 | Bob | Johnson | 1 | 55000 4 | Alice | Williams | 3 | 70000 5 | Charlie | Brown | null | 50000
departments table
id | name | location ----+-------------+---------- 1 | Sales | New York 2 | Marketing | Chicago 3 | Engineering | Seattle 4 | HR | Boston
2. INNER JOIN
INNER JOIN returns only the matching records from both tables.
SELECT e.first_name, e.last_name, d.name as department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;Result: first_name | last_name | department -----------+-----------+------------ John | Doe | Sales Jane | Smith | Marketing Bob | Johnson | Sales Alice | Williams | Engineering
Key Point
Notice that Charlie Brown is not in the result because their dept_id is null, meaning there's no matching department.
3. LEFT OUTER JOIN
Including Employees Without Departments
SELECT e.first_name, e.last_name, d.name as department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;Result: first_name | last_name | department -----------+-----------+------------ John | Doe | Sales Jane | Smith | Marketing Bob | Johnson | Sales Alice | Williams | Engineering Charlie | Brown | null
Key Point
LEFT JOIN keeps all records from the left table (employees) even if there's no matching department.
4. Multiple Table Joins
Joining Three Tables
SELECT
e.first_name,
d.name as department,
p.name as project
FROM employees e
JOIN departments d ON e.dept_id = d.id
LEFT JOIN project_assignments pa ON e.id = pa.employee_id
LEFT JOIN projects p ON pa.project_id = p.id;Performance Tip
When joining multiple tables, the order of joins can affect performance. Start with the tables that will filter out the most rows.