SQL Exercise: Departments which does not have any employee
6. From the following tables, write a SQL query to find all departments, including those without employees. 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, D.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, D.department_id, D.department_name
-- Performing a RIGHT 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
RIGHT 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
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
| | 200 | Operations
| | 130 | Corporate Tax
| | 160 | Benefits
| | 270 | Payroll
| | 260 | Recruiting
| | 120 | Treasury
| | 250 | Retail Sales
| | 210 | IT Support
| | 230 | IT Helpdesk
| | 190 | Contracting
| | 170 | Manufacturing
| | 240 | Government Sales
| | 150 | Shareholder Services
| | 180 | Construction
| | 220 | NOC
| | 140 | Control And Credit
(122 rows)
Code Explanation:
The said query in SQL that selects the first name, last name, department ID, and department name of all departments and their employees, including departments that have no employees. The query retrieves data from the employees and departments tables.
The RIGHT OUTER JOIN clause joins the departments table with the employees table using the department_id column, which is common to both tables. This type of join returns all rows from the right table that is departments, and the matching rows from the left table that is employees, and if there is no match on the left table, it returns NULL values for the columns of that table.
Relational Algebra Expression:
Relational Algebra Tree:
Visual Presentation:
Alternative Solutions:
Using LEFT JOIN:
SELECT E.first_name, E.last_name, D.department_id, D.department_name
FROM departments D
LEFT JOIN employees E ON E.department_id = D.department_id;
Using INNER JOIN and UNION ALL:
SELECT E.first_name, E.last_name, D.department_id, D.department_name
FROM employees E
INNER JOIN departments D ON E.department_id = D.department_id
UNION ALL
SELECT NULL AS first_name, NULL AS last_name, D.department_id, D.department_name
FROM departments D
WHERE NOT EXISTS (
SELECT 1
FROM employees E
WHERE E.department_id = D.department_id
);
Go to:
PREV : Employees who contain a letter z to their first name.
NEXT : Employees who earn less than employee 182.
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.