PostgreSQL Subquery: Find the names of the employees who works in the IT department

2. Write a SQL subquery to find the first_name and last_name of all employees who works in the IT department.

Sample Solution:

Code:

-- This SQL query retrieves the first name and last name of employees who belong to the IT department.

SELECT first_name, -- Selects the first_name column from the employees table
       last_name -- Selects the last_name column from the employees table
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE department_id -- Filters the rows to include only those where the department_id matches the department_id of the IT department
      IN (SELECT department_id -- Subquery: Selects the department_id of the IT department
          FROM departments 
          WHERE department_name = 'IT');

Explanation:

• This SQL query retrieves the first name and last name of employees who belong to the IT department.
• The outer SELECT statement retrieves the first name and last name from the employees table.
• The WHERE clause filters the rows to include only those where the department_id is in the set of department_ids obtained from the subquery.
• The subquery selects the department_id of the IT department.
• The subquery is enclosed in parentheses and executed before the outer query.

Sample table: employees

Output:

pg_exercises=# SELECT first_name, last_name
pg_exercises-# FROM employees
pg_exercises-# WHERE department_id
pg_exercises-# IN (SELECT department_id
pg_exercises(# FROM departments
pg_exercises(# WHERE department_name='IT');
 first_name | last_name
------------+-----------
 Alexander  | Hunold
 Bruce      | Ernst
 David      | Austin
 Valli      | Pataballa
 Diana      | Lorentz
(5 rows)

Practice Online

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

Previous: Write a query to find the first_name, last_name and salaries of the employees who have a higher salary than the employee whose last_name is Bull.
Next: Write a SQL subquery to find the first_name and last_name of the employees under a manager who works for a department based in the United States.