SQL Exercise: Display employees who have or have not any department

SQL JOINS on HR Database: Exercise-10 with Solution

10. From the following tables, write a SQL query to find out which employees have or do not have a department. Return first name, last name, department ID, department name.

Sample table: departments

Sample table: employees

Sample Solution:

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

-- Performing a LEFT OUTER JOIN between the 'employees' table (aliased as 'E') and the 'departments' table (aliased as 'D') based on the 'department_id' column
FROM employees E 

LEFT OUTER JOIN departments D 
  ON E.department_id = D.department_id;

Sample Output:

first_name	last_name	department_id	department_name
Steven		King		90		Executive
Neena		Kochhar		90		Executive
Lex		De Haan		90		Executive
Alexander	Hunold		60		IT
Bruce		Ernst		60		IT
David		Austin		60		IT
Valli		Pataballa	60		IT
Diana		Lorentz		60		IT
Nancy		Greenberg	100		Finance
Daniel		Faviet		100		Finance
John		Chen		100		Finance
Ismael		Sciarra		100		Finance
Jose Manuel	Urman		100		Finance
Luis		Popp		100		Finance
Den	Raphaely		30		Purchasing
Alexander	Khoo		30		Purchasing
Shelli		Baida		30		Purchasing
Sigal		Tobias		30		Purchasing
Guy		Himuro		30		Purchasing
Karen		Colmenares	30		Purchasing
Matthew		Weiss		50		Shipping
Adam		Fripp		50		Shipping
Payam		Kaufling	50		Shipping
Shanta		Vollman		50		Shipping
Kevin		Mourgos		50		Shipping
Julia		Nayer		50		Shipping
Irene		Mikkilineni	50		Shipping
James		Landry		50		Shipping
Steven		Markle		50		Shipping
Laura		Bissot		50		Shipping
Mozhe		Atkinson	50		Shipping
James		Marlow		50		Shipping
TJ		Olson		50		Shipping
Jason		Mallin		50		Shipping
Michael		Rogers		50		Shipping
Ki		Gee		50		Shipping
Hazel		Philtanker	50		Shipping
Renske		Ladwig		50		Shipping
Stephen		Stiles		50		Shipping
John		Seo		50		Shipping
Joshua		Patel		50		Shipping
Trenna		Rajs		50		Shipping
Curtis		Davies		50		Shipping
Randall		Matos		50		Shipping
Peter		Vargas		50		Shipping
John		Russell		80		Sales
Karen		Partners	80		Sales
Alberto		Errazuriz	80		Sales
Gerald		Cambrault	80		Sales
Eleni		Zlotkey		80		Sales
Peter		Tucker		80		Sales
David		Bernstein	80		Sales
Peter		Hall		80		Sales
Christopher	Olsen		80		Sales
Nanette		Cambrault	80		Sales
Oliver		Tuvault		80		Sales
Janette		King		80		Sales
Patrick		Sully		80		Sales
Allan		McEwen		80		Sales
Lindsey		Smith		80		Sales
Louise		Doran		80		Sales
Sarath		Sewall		80		Sales
Clara		Vishney		80		Sales
Danielle	Greene		80		Sales
Mattea		Marvins		80		Sales
David		Lee		80		Sales
Sundar		Ande		80		Sales
Amit		Banda		80		Sales
Lisa		Ozer		80		Sales
Harrison	Bloom		80		Sales
Tayler		Fox		80		Sales
William		Smith		80		Sales
Elizabeth	Bates		80		Sales
Sundita		Kumar		80		Sales
Ellen		Abel		80		Sales
Alyssa		Hutton		80		Sales
Jonathon	Taylor		80		Sales
Jack		Livingston	80		Sales
Kimberely	Grant		0	
Charles		Johnson		80		Sales
Winston		Taylor		50		Shipping
Jean		Fleaur		50		Shipping
Martha		Sullivan	50		Shipping
Girard		Geoni		50		Shipping
Nandita		Sarchand	50		Shipping
Alexis		Bull		50		Shipping
Julia		Dellinger	50		Shipping
Anthony		Cabrio		50		Shipping
Kelly		Chung		50		Shipping
Jennifer	Dilly		50		Shipping
Timothy		Gates		50		Shipping
Randall		Perkins		50		Shipping
Sarah		Bell		50		Shipping
Britney		Everett		50		Shipping
Samuel		McCain		50		Shipping
Vance		Jones		50		Shipping
Alana		Walsh		50		Shipping
Kevin		Feeney		50		Shipping
Donald		OConnell	50		Shipping
Douglas		Grant		50		Shipping
Jennifer	Whalen		10		Administration
Michael		Hartstein	20		Marketing
Pat		Fay		20		Marketing
Susan		Mavris		40		Human Resources
Hermann		Baer		70		Public Relations
Shelley		Higgins		110		Accounting
William		Gietz		110		Accounting

Code Explanation:

The said query in SQL that retrieves the first name, last name, department ID, and department name of each employee. It uses a left outer join to match each employee with their corresponding department based on the department ID in the employees table.
The resulting output will have four columns that is "first_name", "last_name", "department_id", and "department_name". The "first_name" and "last_name" columns will contain the first and last name of each employee, while the "department_id" column will contain the ID of the department associated with each employee. The "department_name" column will contain the name of the department associated with each employee.
In this query, it is assumes that the employees table has a column named "department_id" which references the ID of the department associated with each employee, and that the departments table has columns named "department_id" and "department_name" which contain the ID and name of each department. The left outer join is used in case an employee does not have a department associated with them, so that the query will still return information about that employee.

Relational Algebra Expression:

Relational Algebra Tree:

Visual Presentation:

Alternative Solutions:

Using RIGHT JOIN:

SELECT E.first_name, E.last_name, E.department_id, D.department_name
FROM departments D
RIGHT JOIN employees E ON E.department_id = D.department_id;

Using INNER JOIN and UNION ALL:

SELECT E.first_name, E.last_name, E.department_id, D.department_name
FROM employees E
INNER JOIN departments D ON E.department_id = D.department_id
UNION ALL
SELECT E.first_name, E.last_name, E.department_id, NULL AS department_name
FROM employees E
WHERE NOT EXISTS (
    SELECT 1
    FROM departments D
    WHERE E.department_id = D.department_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: Display the department name, city for each department.
Next SQL Exercise: Display employees and their manager.