Difference between corelated subquery and windows function

 We are given employees table as below.

And our requirement is to get an output in 3 columns. First Name, Department and total no. of employees per department.

One of the ways to achieve this is by using correlational sub-query.

Something like this...

We are relating inner query(sub-query) with outer query on department column so inner query is executed for every record that exists in outer query.

If employees table has 1000 records, then inner query is run for all 1000 records. Understandably, this is a very slow process for large data tables and also an expensive one as these days most companies are paying for the cloud usage per time used basis.

Alternative way to do this by using windows function OVER( )

The advantage is that it is much quicker and we can use it to group data using any column without using GROUP BY function.

We can use OVER( ) for any aggregate functions like SUM or AVG or MAX or MIN etc.

And if we want to ensure that all records are returned in both methods, we can use EXCEPT to see if any difference.

This confirms that both are getting same results