SQL Exercise: Departments which does not have any employee

SQL JOINS on HR Database: Exercise-6 with Solution

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
);

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Employees who contain a letter z to their first name.
Next SQL Exercise: Employees who earn less than employee 182.