Suppose you want to obtain those employees who work for departments with more than 5 persons. This is not the best query to solve this data question, but this is a good example to show what is allowed by the CROSS APPLY join:

select emp_id, last_name
from employee e
join (select dept_id, count(*) dept_population from department 
      where dept_id = e.dept_id group by 1) d 
     on d.dep_id = e.dep_id 
where d.dept_population  > 5;

The previous query fails because in the subquery after the join clause we can’t refer to any table outside the subquery. In our example the reference to e.dept_id is invalid. In the following query this issue is fixed by using a CROSS APPLY instead of a JOIN.

select emp_id, last_name
from employee e
cross apply (select dept_id, count(*) dept_population 
             from departments where dept_id = e.dept_id group by dept_id) d  
where d.dept_population  > 5;