﻿ SQL - Display employees not working in certain departments

# SQL Exercises: Display employees not working in certain departments

## SQL SUBQUERY: Exercise-11 with Solution

From the following tables, write a SQL query to find those employees who do not work in the departments where managers’ IDs are between 100 and 200 (Begin and end values are included.). Return all the fields of the employees.

Sample table: employees

Sample table: departments

Sample Solution:

``````SELECT *
FROM employees
WHERE department_id NOT IN
(SELECT department_id
FROM departments
WHERE manager_id BETWEEN 100 AND 200);
``````

Sample Output:

```employee_id	first_name	last_name	email	phone_number		hire_date	job_id	salary	commission_pct	manager_id	department_id
178		Kimberely	Grant		KGRANT	011.44.1644.429263 	2007-05-24	SA_REP	7000.00		0.15	149		0
201		Michael		Hartstein	MHARTSTE515.123.5555		2004-02-17	MK_MAN	13000.00	0.00	100		20
202		Pat		Fay		PFAY	603.123.6666		2005-08-17	MK_REP	6000.00		0.00	201		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
206		William		Gietz		WGIETZ	515.123.8181		2002-06-07	AC_ACCOUNT8300.00	0.00	205		110
```

Code Explanation:

The said query that retrieves all columns (denoted by *) from the employees table where the department ID does not match any of the department IDs in the departments table with a manager ID between 100 and 200, inclusive. The subquery in the NOT IN clause retrieves the department IDs from the departments table that meet the specified manager ID criteria. The result of the query will be all employees who are not associated with departments that have a manager ID between 100 and 200.

Visual Presentation:

Alternative Statements:

Using JOIN:

``````
SELECT e.*
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.manager_id NOT BETWEEN 100 AND 200 OR d.manager_id IS NULL;
``````

Using EXISTS:

``````
SELECT *
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM departments d
WHERE e.department_id = d.department_id
AND d.manager_id BETWEEN 100 AND 200
);
``````

Duration:

Rows:

Cost: