SQL Interview Question - 3

Consider two tables, employees and departments, with the following structures:

    
    employees:
    | employee_id | employee_name | department_id |
    |-------------|---------------|---------------|
    | 1           | Alice         | 1             |
    | 2           | Bob           | 2             |
    | 3           | Charlie       | 1             |
    | 4           | David         | 3             |

    departments:
    | department_id | department_name |
    |---------------|-----------------|
    | 1             | Sales           |
    | 2             | Marketing       |
    | 3             | Finance         |
    
    

Write an SQL query to list all employees along with their department names. If an employee does not belong to any department, the department name should be displayed as "No Department".

Solution:

    
    SELECT e.employee_name, COALESCE(d.department_name, 'No Department') AS department_name
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id;
    
    

Explanation:

• We use a LEFT JOIN to ensure all rows from the employees table are included in the result, regardless of whether there is a matching row in the departments table.
• The COALESCE function is used to replace NULL values in the department_name column with "No Department", which happens when an employee does not belong to any department.
• We join the employees table with the departments table on the department_id column to match employees with their respective departments.
• The COALESCE function ensures that if there's no matching department for an employee, it will display "No Department" instead of a NULL department name.

 

Share your alternate solution in comments.