Can anyone provide some insight as to why one query might be more optimal than the other?
There are three queries that return the same data from a one to many table join.
Statement1 uses the keyword "DISTINCT"
Statement2 uses a COUNT() to group the data
Statement3 uses EXISTS instead of a join:
-- Statement 1 SELECT DISTINCT d.dept_id, d.dept FROM dept d INNER JOIN employee e ON e.dept = e.dept WHERE d.dept = 1; -- Statement 2 SELECT COUNT(d.dept_id), d.dept_id, d.dept FROM dept d INNER JOIN employee e ON e.dept = e.dept WHERE d.dept = 1 GROUP BY d.dept_id, d.dept; -- Statement 3 SELECT d.dept_id, d.dept FROM dept d WHERE EXISTS ( SELECT e.dept FROM employee e WHERE d.dept = 1);
You can see which tables did the high reads etc and go from there. I am not a big fan of SQL plans which sometimes don't tell the whole story.