Challenge #4

Question

Consider the following employees and departments tables:

employees

departments

Understanding the tables

The department_id column in the employees table establishes a foreign key relationship with the department_id column in the departments table.

This relationship allows each employee record to be associated with a specific department. The department_id in the employees table refers to the department_id in the departments table, indicating the department in which the employee works.

Can you identify the employees who work in the same department?

Exactly! They are Employee 1 - John Doe and Employee 3 - Bob Johnson, who work in Department 101 that is Sales.

Employee 4 - Alice Brown is not assigned to any department (NULL value in the department_id column).

There are no employees working in the Department 104 - IT.

Exercise

Write a SQL query to retrieve a list of all employees and their corresponding department names. Include all employees, even if they are not assigned to any department.

The solution to this exercise can be found below - please take some time to work on the solution by yourself first before reading any further.

Solution

Query

SELECT emp.employee_id, emp.employee_name, emp.salary, dept.department_name 
  FROM employees emp 
  LEFT JOIN departments dept 
    ON emp.department_id = dept.department_id;        

Result

In the query, we use a LEFT JOIN to combine the employees and departments tables based on the common column department_id. In the exercise, we have been asked to include all employees, even if they are not assigned to any department. A LEFT JOIN is suitable for this. This type of join returns all rows from the left table (employees) and the matched rows from the right table (departments). If there is a match, the corresponding department name is displayed. If there is no match, NULL values are returned for columns from the right table; example, the Employee 4 - Alice Brown is not in any department, therefore the value NULL is displayed in the department_name column in the result for this employee.

SQL Tips

1. Left Vs Right table?: How to identify which table is the left one and which table is the right one when using joins in a SQL statement? The table specified in the query that is to the left of the type of JOIN (irrespective of the type of join you are using) is considered as the left table, and the one to the right of the type of JOIN being used is considered as the right table. Yes, as simple as that!
2. Understanding LEFT JOIN: It is a type of OUTER JOIN that retrieves all rows from the left table and the matching rows from the right table. If there is a match the corresponding value is displayed. If there is no match, NULL values are returned for columns from the right table. Note that not all rows from the right table will be retrieved in the result when using a LEFT JOIN, example Department 104 - IT: because it does not have a match in the employees table.
3. Column Aliases: Use column aliases (emp and dept in this example) to make the SQL query more readable. Aliases can be especially helpful when dealing with complex queries involving multiple tables. It's also used to prevent from repeating long table names; imagine having to specify a long table name like employees_parttime_uk_developers instead of its alias alongside each column you refer from that table in the statement! It would have definitely made the query less readable. Moreover, use meaning alias names, such as emp for employees and dept for departments.
4. Foreign Key column: The foreign key column can contain duplicates; such as in our case, for the Employee 1 - John Doe and Employee 3 - Bob Johnson whereby both have the same department_id: 101, that is the Sales department. The foreign key column can contain NULL values as well.

Feel free to comment your findings!

If you don't want to miss the carefully crafted SQL challenges every week, subscribe to my Newsletter. Elevate your skills through practice and unlock expert tips; be on top of your game!