Employees with Department Names (INNER JOIN)
Return employee_id, first_name, last_name, and department_name for matched employees, ordered by employee_id.
- Joins
- Sorting
Exercise brief
Understand the request
People operations analyst A directory export needs the department label for every employee whose department relationship is valid.
List every employee with their department name. Only include employees that ARE assigned to a department. Return employee_id, first_name, last_name, department_name — ordered by employee_id.
Return
- Return one row per matched employee.
- Order by employee_id.
Constraints
- Use an explicit equality INNER JOIN on department_id.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)department_idINTEGER
departments
department_idINTEGERdepartment_nameVARCHAR(50)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
INNER JOIN syntax: `FROM table1 a INNER JOIN table2 b ON a.key = b.key`. Only rows with matches on BOTH sides survive.
Hint 2
Aliases (e, d) let you write short column references and disambiguate when both tables share a column name.
Hint 3
ON e.department_id = d.department_id is the join predicate — it's HOW the rows pair up.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id ORDER BY e.employee_id;Why this works
INNER JOIN is the workhorse — it keeps only the intersection. If an employee has NULL department_id (or a value with no match in departments), they will NOT appear. That's by design; switch to LEFT JOIN if you want to keep them anyway.
Success check
Every employee with a valid department appears exactly once with the correct department name.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | first_name | last_name | department_name |
|---|---|---|---|
| 100 | John | Smith | IT |
| 101 | Alice | Johnson | IT |
| 102 | Bob | Wilson | IT |
| 103 | Carol | Davis | HR |
| 104 | David | Brown | Finance |
| 105 | Emma | Taylor | Marketing |
| 106 | Frank | Green | Marketing |
| 107 | Grace | White | IT |
| 108 | Henry | Clark | HR |
| 109 | Ivy | Martinez | Finance |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
Self Joins & Hierarchical Queries
Query organization charts, trees, and parent-child relationships.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
Open the interactive workspace and practice across SQL topics.