Performance between the subquery and join?

Both queries generates a list of department IDs along with the number
of empl开发者_运维知识库oyees assigned to each department. 

I'm able to get results for above both using joins and subquery but I'm very keen to know how both queries works in terms of performance which is better: joins or subquery. I've added Explain Plan screen shot for both queries, but I don't understand what it means.

Using Join

SELECT d.dept_id, d.name, count(emp_id) AS num_employee
FROM department d INNER JOIN employee e ON e.dept_id = d.dept_id
GROUP BY dept_id;

Using Subquery

SELECT d.dept_id, d.name, e_cnt.how_many num_employees
FROM department d INNER JOIN
(SELECT dept_id, COUNT(*) how_many
FROM employee
GROUP BY dept_id) e_cnt
ON d.dept_id = e_cnt.dept_id;

---

The join is clearly better as you can see in your execution plan. :P

The subselect is using an index to get the initial table (count (*), dept_id) and then is using a buffer table to join to the outer select statement to get you your result.

The inner join uses the index on both department and employee to determine the matching rows saving yourself the creation of the buffer table and the initial index seek.