SQL Exercise: Employees who work in the same department as Taylor

SQL JOINS on HR Database: Exercise-12 with Solution

12. From the following tables, write a SQL query to find the employees who work in the same department as the employee with the last name Taylor. Return first name, last name and department ID.

Sample table: employees

Sample Solution:

-- Selecting specific columns (E.first_name, E.last_name, E.department_id) from the 'employees' table, aliased as 'E'
SELECT E.first_name, E.last_name, E.department_id 

-- Performing an INNER JOIN between the 'employees' table (aliased as 'E') and the 'employees' table (aliased as 'S') based on the condition that 'E.department_id' is equal to 'S.department_id' and 'S.last_name' is 'Taylor'
FROM employees E 

JOIN employees S
  ON E.department_id = S.department_id
   AND S.last_name = 'Taylor';

Sample Output:

first_name	last_name	department_id
Matthew		Weiss		50
Adam		Fripp		50
Payam		Kaufling	50
Shanta		Vollman		50
Kevin		Mourgos		50
Julia		Nayer		50
Irene		Mikkilineni	50
James		Landry		50
Steven		Markle		50
Laura		Bissot		50
Mozhe		Atkinson	50
James		Marlow		50
TJ		Olson		50
Jason		Mallin		50
Michael		Rogers		50
Ki		Gee		50
Hazel		Philtanker	50
Renske		Ladwig		50
Stephen		Stiles		50
John		Seo		50
Joshua		Patel		50
Trenna		Rajs		50
Curtis		Davies		50
Randall		Matos		50
Peter		Vargas		50
John		Russell		80
Karen		Partners	80
Alberto		Errazuriz	80
Gerald		Cambrault	80
Eleni		Zlotkey		80
Peter		Tucker		80
David		Bernstein	80
Peter		Hall		80
Christopher	Olsen		80
Nanette		Cambrault	80
Oliver		Tuvault		80
Janette		King		80
Patrick		Sully		80
Allan		McEwen		80
Lindsey		Smith		80
Louise		Doran		80
Sarath		Sewall		80
Clara		Vishney		80
Danielle	Greene		80
Mattea		Marvins		80
David		Lee		80
Sundar		Ande		80
Amit		Banda		80
Lisa		Ozer		80
Harrison	Bloom		80
Tayler		Fox		80
William		Smith		80
Elizabeth	Bates		80
Sundita		Kumar		80
Ellen		Abel		80
Alyssa		Hutton		80
Jonathon	Taylor		80
Jack		Livingston	80
Charles		Johnson		80
Winston		Taylor		50
Jean		Fleaur		50
Martha		Sullivan	50
Girard		Geoni		50
Nandita		Sarchand	50
Alexis		Bull		50
Julia		Dellinger	50
Anthony		Cabrio		50
Kelly		Chung		50
Jennifer	Dilly		50
Timothy		Gates		50
Randall		Perkins		50
Sarah		Bell		50
Britney		Everett		50
Samuel		McCain		50
Vance		Jones		50
Alana		Walsh		50
Kevin		Feeney		50
Donald		OConnell	50
Douglas		Grant		50

Code Explanation:

The said query in SQL that retrieves the first name, last name, and department ID of all employees who work in the same department as an employee with the last name 'Taylor'. It uses a join to match each employee with another employee in the same department as them, who also has the last name 'Taylor'.
It is assume that the employees table contains at least one employee with the last name 'Taylor' and that this employee's department ID is not NULL. If there are multiple employees with the last name 'Taylor' in different departments, this query will only retrieve employees who work in the same department as one of those employees.

Relational Algebra Expression:

Relational Algebra Tree:

Visual Presentation:

Alternative Solutions:

Using a Correlated Subquery with EXISTS:

SELECT E.first_name, E.last_name, E.department_id 
FROM employees E 
WHERE EXISTS (
    SELECT 1 
    FROM employees S 
    WHERE S.last_name = 'Taylor' 
    AND E.department_id = S.department_id
);

Explanation:

This query uses a correlated subquery with EXISTS to check if there exists an employee with the last name 'Taylor' in the same department as each employee in the main query.

Using a Self-Join and WHERE Clause:

SELECT E.first_name, E.last_name, E.department_id 
FROM employees E, employees S
WHERE E.department_id = S.department_id
AND S.last_name = 'Taylor';

Explanation:

This query uses a self-join by referencing the employees table twice (as E and S). It selects employees whose 'department_id' matches and whose last name is 'Taylor'.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Display employees and their manager.
Next SQL Exercise: Jobs which started between two given dates.