INNER JOIN

---

title: “INNER JOIN” part: 1 topic_number: 15 slug: “inner-join” difficulty: “Beginner” prerequisites: “primary-foreign-keys, select-from-where” —

INNER JOIN

What Is It?

INNER JOIN combines rows from two tables based on a related column. It returns only rows where there’s a match in BOTH tables. Think of it as finding the intersection — only the data that exists on both sides.

Real-world analogy: Matching employees with their department information. If an employee has no department, or a department has no employees, those won’t appear in an INNER JOIN result.

---

Syntax Breakdown

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Breaking it down:

• FROM table1 — The “left” table
• INNER JOIN table2 — The “right” table to join with
• ON table1.column = table2.column — The matching condition (usually foreign key = primary key)

Key rule: INNER JOIN returns rows only when the ON condition finds a match in BOTH tables.

---

Basic Example

Show employees with their department names:

SELECT 
    employees.first_name,
    employees.last_name,
    departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

What this does:

1. For each employee, look up their department_id
2. Find the matching department in the departments table
3. Combine the data into one result row
4. Only include employees who have a matching department

Expected output (partial):

first_name	last_name	department_name
Alice	Johnson	Engineering
Bob	Smith	Engineering
Carol	Williams	Marketing
David	Brown	Marketing
Eve	Davis	Sales

Missing: Paul Garcia (has NULL department_id) — no match, so excluded.

---

Going Deeper

Table Aliases for Cleaner Queries

SELECT 
    e.first_name,
    e.last_name,
    e.salary,
    d.department_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

What changed:

• employees e — Give employees the alias “e”
• departments d — Give departments the alias “d”
• Use e.column and d.column instead of full table names

Why this matters: Much more readable, especially with multiple joins!

Joining Multiple Tables

Show employees, their departments, AND their project assignments:

SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    p.project_name,
    ep.role
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN employee_projects ep ON e.employee_id = ep.employee_id
INNER JOIN projects p ON ep.project_id = p.project_id
ORDER BY e.last_name;

What this does:

• Joins employees with departments
• Then joins with employee_projects (assignments)
• Then joins with projects (project details)
• Returns one row per assignment

Expected output (partial):

first_name	last_name	department_name	project_name	role
Jack	Anderson	Engineering	Mobile App Launch	Senior Developer
Jack	Anderson	Engineering	Data Migration	Migration Specialist
Carol	Williams	Marketing	Marketing Campaign Q1	Campaign Manager

Note: Jack appears twice (he’s on 2 projects). INNER JOIN creates a row for each combination.

Filtering Joined Results

Show Engineering employees and their projects:

SELECT 
    e.first_name,
    e.last_name,
    p.project_name
FROM employees e
INNER JOIN employee_projects ep ON e.employee_id = ep.employee_id
INNER JOIN projects p ON ep.project_id = p.project_id
WHERE e.department_id = 1
ORDER BY e.last_name;

WHERE filters AFTER joining. You get all employee-project combinations, then filter for department 1.

Pause and Predict: How many rows will this return?

Answer

It depends on how many projects Engineering employees are assigned to. Bob, Jack, and others from Engineering appear once for each of their projects. Based on our data, probably around 10-15 rows.

---

Watch Out — Common Mistakes

Mistake #1: Forgetting the ON Clause (Cartesian Product!)

--  WRONG — Creates a Cartesian product!
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d;  -- Missing ON!

What happens: Every employee is matched with EVERY department. If you have 20 employees and 10 departments, you get 200 rows!

Error (in some MySQL modes): Every derived table must have its own alias or no error but wrong results.

-- • CORRECT
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Mistake #2: Ambiguous Column Names

--  WRONG
SELECT employee_id, first_name, department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Error: Column 'employee_id' in field list is ambiguous

Why: If both tables have an employee_id column (unlikely here, but common in other scenarios), MySQL doesn’t know which one you want.

-- • CORRECT — Be explicit
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Best practice: Always prefix columns with table aliases in JOINs, even if not ambiguous. Makes your intent clear.

Mistake #3: Expecting NULL Matches

SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

What beginners expect: “This shows all employees with their department”

What actually happens: Paul Garcia (department_id = NULL) is excluded. INNER JOIN doesn’t match NULLs!

The fix (if you want Paul): Use LEFT JOIN (next topic).

---

Edge Case Spotlight

INNER JOIN vs WHERE (They’re Similar!)

These two queries return the same result:

Using INNER JOIN:

SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Using WHERE (old style, avoid):

SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

Both work, but INNER JOIN is clearer and more standard. The WHERE style is “implicit join” (old SQL-89 syntax). Modern SQL uses explicit JOIN.

Best practice: Always use explicit JOIN syntax (INNER JOIN, LEFT JOIN, etc.). It’s more readable and prevents accidental Cartesian products.

---

Try This

Exercise 1 (Guided)

List all employees and their manager’s name. (Hint: This is a self-join — employees joining with employees on manager_id.)

Hint

```sql SELECT e.first_name AS employee_name, m.first_name AS manager_name FROM employees e INNER JOIN employees m ON e.manager_id = m.employee_id; ``` You need two aliases for the same table!

Exercise 2 (Independent)

Show all projects with at least one employee assigned. Display project_name and count how many employees are on each project. (Hint: Use employee_projects and projects tables, with GROUP BY.)

Exercise 3 (Challenge)

Find employees who work in San Francisco (location = ‘San Francisco’). Show their name, salary, and department name. Sort by salary descending.

---

Answer Key

Exercise 1 Answer

```sql SELECT e.first_name AS employee_first_name, e.last_name AS employee_last_name, m.first_name AS manager_first_name, m.last_name AS manager_last_name FROM employees e INNER JOIN employees m ON e.manager_id = m.employee_id; ``` **Expected output (partial):** | employee_first_name | employee_last_name | manager_first_name | manager_last_name | |---------------------|--------------------|--------------------|-------------------| | Bob | Smith | Alice | Johnson | | Carol | Williams | Alice | Johnson | | David | Brown | Carol | Williams | | Eve | Davis | Alice | Johnson | **Note:** Alice is excluded (she has manager_id = NULL, no match). Paul is also excluded (NULL manager).

Exercise 2 Answer

```sql SELECT p.project_name, COUNT(*) AS employee_count FROM projects p INNER JOIN employee_projects ep ON p.project_id = ep.project_id GROUP BY p.project_name ORDER BY employee_count DESC; ``` **Expected output (partial):** | project_name | employee_count | |--------------|----------------| | Website Redesign | 3 | | Mobile App Launch | 3 | | Marketing Campaign Q1 | 3 | | Sales CRM Upgrade | 3 | **Note:** Projects with no employees assigned don't appear (INNER JOIN excludes them).

Exercise 3 Answer

```sql SELECT e.first_name, e.last_name, e.salary, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id WHERE d.location = 'San Francisco' ORDER BY e.salary DESC; ``` **Expected output:** | first_name | last_name | salary | department_name | |------------|-----------|--------|-----------------| | Alice | Johnson | 120000.00 | Engineering | | Jack | Anderson | 110000.00 | Engineering | | Karen | Thomas | 105000.00 | Product | | Bob | Smith | 95000.00 | Engineering | | Quinn | Martinez | 92000.00 | Engineering | | Leo | Jackson | 88000.00 | Engineering | Employees in Engineering (San Francisco) and Product (San Francisco), sorted by salary.

---

Quick Recap

• INNER JOIN combines rows from two tables where there’s a match
• Returns only rows that exist in BOTH tables
• Use ON clause to specify the matching condition
• Table aliases (e, d) make queries more readable
• Can join multiple tables by chaining INNER JOINs
• NULL values don’t match — excluded from INNER JOIN results
• Always use explicit JOIN syntax (not old WHERE style)

---

Up Next

Time for a Challenge! → Mini Challenge 5

You’ve learned HAVING, keys, and INNER JOINs! Time to practice these foundational concepts.