sqlbeginner10 minutes

Refactor SQL Query to Use WHERE Instead of HAVING for Simple Conditions

Improve the given SQL query by refactoring it to replace the HAVING clause with a WHERE clause where appropriate, optimizing performance without changing the result.

Challenge prompt

You are given a SQL query that uses a HAVING clause to filter rows on a simple condition that could be done using a WHERE clause. Refactor the query to optimize it by using WHERE instead of HAVING, while ensuring the output remains exactly the same. Original query: SELECT employee_id, department_id FROM employees GROUP BY employee_id, department_id HAVING department_id = 3;

Guidance

• Understand that HAVING is used to filter aggregated results, while WHERE filters rows before grouping.
• Check if the condition in HAVING can be applied before aggregation; if yes, use WHERE.
• Refactor the query by moving the condition inside the WHERE clause and removing HAVING.

Hints

• Try running the query with the condition in WHERE and see if the result matches the original.
• Remember, WHERE filters rows before aggregation happens, which is more efficient.
• Use GROUP BY only if necessary for other aggregations.

Starter code

SELECT employee_id, department_id
FROM employees
GROUP BY employee_id, department_id
HAVING department_id = 3;

Expected output

The query returns all unique employee_id and department_id pairs where department_id is 3, identical before and after refactoring.

Core concepts

SQL WHERE clauseSQL HAVING clauseQuery optimization

Challenge a Friend

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