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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/joins-hr/sql-joins-hr-exercise-10.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics