MySQL : xyz_dept and number of employee working in xyz_dept using nested queries -

i retrieve find out no.of employees working in “sales” department along "sales" in retrieved table as:

+-------+------------------+ | name  | count(e.dept_id) | +-------+------------------+ | sales |                3 | +-------+------------------+ 

i can write following query:

mysql> select d.name, count(e.dept_id)     ->   department d, employee e     ->  e.dept_id = d.department_id , d.name='sales'; 

but i've been taught nested queries improve efficiency of query. i'm trying rewrite same using nested query. go this:

mysql> select   count(*)     ->     employee      ->    dept_id = (select department_id     ->                      department     ->                     name='sales'); +----------+ | count(*) | +----------+ |        3 | +----------+ 

i tried many combinations no hope. how can obtain below table using nested query:

+-------+------------------+ | name  | count(e.dept_id) | +-------+------------------+ | sales |                3 | +-------+------------------+ 

thank you.

please try below query:

   select d.name, count(e.dept_id)     employee e    inner join (select name,department_id department name='sales') d           on d.department_id = e.dept_id;