w3resource

MySQL Subquery Exercises: Display the employee ID, first name, last name, and department name of all employees

MySQL Subquery: Exercise-13 with Solution

Write a MySQL query to display the employee ID, first name, last name, and department names of all employees.

Sample table: employees


Sample table : departments


Code:

-- Selecting the employee_id, first name, last name, and department name of employees 
SELECT employee_id, first_name, last_name, 
-- Subquery to fetch the department name for each employee's department_id
(SELECT department_name FROM departments d
 -- Joining the employees table with the departments table based on the department_id
 WHERE e.department_id = d.department_id) department 
-- Selecting data from the employees table, aliasing it as 'e'
 FROM employees e 
-- Sorting the result set based on the department name
 ORDER BY department;

Explanation:

  • This MySQL code selects the employee ID, first name, last name, and department name of employees from the "employees" table.
  • It uses a subquery to fetch the department name for each employee's department_id from the "departments" table.
  • The subquery is correlated with the outer query by matching the department_id of employees with the department_id in the departments table.
  • The result set is then sorted based on the department name using the ORDER BY clause.

MySQL Subquery Syntax:

MySQL subquery syntax

- The subquery (inner query) executes once before the main query (outer query) executes.
- The main query (outer query) use the subquery result.

MySQL SubQueries: Display the employee ID, first name, last names, and department names of all employees.

 

MySQL Code Editor:

Structure of 'hr' database:

hr database

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a MySQL query to find the name (first_name, last_name) of the employees who are not supervisors.
Next:Write a MySQL query to display the employee ID, first name, last name, salary of all employees whose salary is above average for their departments.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.