How to Fix SQL Queries with LEFT JOIN

As someone who has worked on SQL queries to analyze and manipulate data, I understand the frustration of running a query only to discover that certain results are missing, especially when they should be included in the output. This happened when I was working on an analysis for instructors and their salaries. The problem lay in the fact that I was using an implicit inner join, which excluded certain rows that didn’t meet the join condition. Specifically, instructors without higher earners were left out of the results. In this post, I’ll walk you through the solution and provide an enhanced SQL query to get the desired results.

The Issue with the Original Query

The original query used an implicit inner join (FROM instructor as T, instructor as S), which meant that only rows where there was a match between the two tables (instructors with higher salaries) were returned. If there were no higher earners, those rows (like “Einstein”, the instructor with the highest salary) were excluded from the results.

Fix the Query with LEFT JOIN

To solve this issue, I replaced the implicit inner join with a LEFT JOIN. Here’s why:

• A LEFT JOIN ensures that all rows from the left table (instructors in this case) are retained, even if there are no matching rows in the right table (higher-earning instructors).
• If no matching higher earner is found, the values from the right table will be NULL, and we can calculate the number of higher earners using COUNT(S.ID) as 0.

This fix ensures that even instructors without higher earners are included in the results.

Step-by-Step Breakdown of the Corrected Query

Here’s the corrected query:

SELECT 
    T.ID, 
    T.name, 
    T.salary, 
    COUNT(S.ID) AS num_higher_earners
FROM 
    instructor AS T
LEFT JOIN 
    instructor AS S ON S.salary > T.salary
GROUP BY 
    T.ID, T.name, T.salary
ORDER BY 
    T.salary DESC;

Enhancing the Query Functionality

After fixing the main issue, I decided to enhance the query by adding more context and improving the readability of the results. Below are a few enhancements I made:

Include Department Name

To provide more context, I added the department name (dept_name) to the output. This helps to understand not only how an instructor’s salary compares but also which department they belong to.

SELECT 
    T.ID, 
    T.name, 
    T.dept_name, 
    T.salary, 
    COUNT(S.ID) AS num_higher_earners
FROM 
    instructor AS T
LEFT JOIN 
    instructor AS S ON S.salary > T.salary
GROUP BY 
    T.ID, T.name, T.dept_name, T.salary
ORDER BY 
    T.salary DESC;

Format Salary for Readability

Displaying salaries with commas makes them much easier to read. I used the FORMAT() function to format the salary to two decimal places.

SELECT 
    T.ID, 
    T.name, 
    T.dept_name, 
    FORMAT(T.salary, 2) AS formatted_salary, 
    COUNT(S.ID) AS num_higher_earners
FROM 
    instructor AS T
LEFT JOIN 
    instructor AS S ON S.salary > T.salary
GROUP BY 
    T.ID, T.name, T.dept_name, T.salary
ORDER BY 
    T.salary DESC;

Calculate Salary Rank

If you want to take things a step further, you can use window functions to calculate the rank of each instructor based on their salary. For this, I used the DENSE_RANK() function, which assigns ranks with no gaps.

SELECT 
    ID, 
    name, 
    salary, 
    (COUNT(*) OVER (ORDER BY salary DESC) - 1) AS num_higher_earners,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM 
    instructor
ORDER BY 
    salary DESC;

Filter by Department

Sometimes, you may want to focus on a particular department, such as “Computer Science”. I added a WHERE clause to filter the results by department.

SELECT 
    T.ID, 
    T.name, 
    T.dept_name, 
    T.salary, 
    COUNT(S.ID) AS num_higher_earners
FROM 
    instructor AS T
LEFT JOIN 
    instructor AS S ON S.salary > T.salary
WHERE 
    T.dept_name = 'Comp. Sci.'
GROUP BY 
    T.ID, T.name, T.dept_name, T.salary
ORDER BY 
    T.salary DESC;

Key Takeaways

• LEFT JOIN: Always use a LEFT JOIN when you want to ensure that all rows from the left table are included, even if there are no matching rows in the right table.
• Formatting: Use the FORMAT() function for better readability, especially when dealing with monetary values.
• Window Functions: Leverage window functions like DENSE_RANK() for advanced analytics and rankings.
• Department Filtering: Use the WHERE clause to filter by department and provide more context for your results.

Final Thoughts

By fixing the join type to LEFT JOIN, I was able to include all instructors in the result set, even those without higher earners. Additionally, I enhanced the query by adding the department name, formatting the salary, calculating the salary rank, and filtering by department. These steps significantly improved the analysis and presentation of the data.

If you’re working with similar data, using these SQL techniques will help you generate comprehensive and easily interpretable reports. I hope this explanation and enhanced solution give you a better understanding of how to work with SQL joins and improve query functionality.