SQL Exercises: Details of employees who manage a department

SQL SUBQUERY: Exercise-33 with Solution

From the following table, write a SQL query to find those employees who manage a department. Return all the fields of employees table.

Sample table: employees

Sample table: departments

Sample Solution:

SELECT * 
FROM employees 
WHERE employee_id=ANY 
( SELECT manager_id FROM departments );

Sample Output:

employee_id	first_name	last_name	email	phone_number	hire_date	job_id	salary	commission_pct	manager_id	department_id
100		Steven		King		SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	0.00	0		90
103		Alexander	Hunold		AHUNOLD	590.423.4567	2006-01-03	IT_PROG	9000.00		0.00	102		60
108		Nancy		Greenberg	NGREENBE515.124.4569	2002-08-17	FI_MGR	12000.00	0.00	101		100
114		Den		Raphaely	DRAPHEAL515.127.4561	2002-12-07	PU_MAN	11000.00	0.00	100		30
121		Adam		Fripp		AFRIPP	650.123.2234	2005-04-10	ST_MAN	8200.00		0.00	100		50
145		John		Russell		JRUSSEL	011.44.1344.4292682004-10-01    SA_MAN	14000.00	0.40	100		80
200		Jennifer	Whalen		JWHALEN	515.123.4444	2003-09-17	AD_ASST	4400.00		0.00	101		10
201		Michael		Hartstein	MHARTSTE515.123.5555	2004-02-17	MK_MAN	13000.00	0.00	100		20
203		Susan		Mavris		SMAVRIS	515.123.7777	2002-06-07	HR_REP	6500.00		0.00	101		40
204		Hermann		Baer		HBAER	515.123.8888	2002-06-07	PR_REP	10000.00	0.00	101		70
205		Shelley		Higgins		SHIGGINS515.123.8080	2002-06-07	AC_MGR	12000.00	0.00	101		110

Code Explanation:

The said query in SQL that retrieves all the data from the 'employees' table where the "employee_id" matches any of the "manager_id" values from the 'departments' table.
The subquery "( SELECT manager_id FROM departments )" retrieves the "manager_id" values from the 'departments' table, and the "ANY" operator checks if any of those values match the "employee_id" in the 'employees' table.

Visual Presentation:

Alternative Statements:

Using Subquery with IN:

SELECT * 
FROM employees 
WHERE employee_id IN 
( 
    SELECT manager_id 
    FROM departments 
    WHERE manager_id IS NOT NULL
);

Using Subquery with EXISTS:

SELECT e.* 
FROM employees e
WHERE EXISTS 
(
    SELECT 1 
    FROM departments d 
    WHERE d.manager_id = e.employee_id
);

Using a Join:

SELECT DISTINCT e.* 
FROM employees e
JOIN departments d ON e.employee_id = d.manager_id;

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Find out the details of employees who are managers.
Next SQL Exercise: Salary earn by the employee which is maximum.