sqlbeginner10 minutes

Refactor a SQL Query to Simplify and Optimize Filtering

Improve a given SQL query by refactoring it for clarity and better performance while maintaining the same output. This challenge focuses on rewriting a query that uses unnecessary subqueries and redundant filtering.

Challenge prompt

You are given a SQL query that fetches the names of employees who belong to the 'Sales' department and whose salary is above 50000. The current query uses nested subqueries and redundant WHERE conditions making it harder to read and potentially less efficient. Refactor the query to remove unnecessary subqueries and simplify the WHERE clause but ensure the output remains exactly the same. Here is the original query: SELECT name FROM employees WHERE department_id IN ( SELECT id FROM departments WHERE name = 'Sales' AND id IN ( SELECT department_id FROM employees WHERE salary > 50000 ) );

Guidance

• Focus on flattening the query by removing nested IN clauses when possible.
• Combine filtering conditions logically to reduce complexity.
• Make sure the refactored query returns the same employee names as the original.

Hints

• Consider joining the tables instead of using multiple nested IN subqueries.
• Filtering by salary can be done in the main WHERE clause to avoid nested subqueries.

Starter code

SELECT name FROM employees
WHERE department_id IN (
  SELECT id FROM departments
  WHERE name = 'Sales' AND id IN (
    SELECT department_id FROM employees WHERE salary > 50000
  )
);

Expected output

List of employee names who work in the 'Sales' department and earn more than 50000.

Core concepts

WHERE clause simplificationSubquery refactoringBasic SQL filtering

Challenge a Friend

Send this duel to someone else and see if they can solve it.