My issue
- The same task could have been done by a WHERE clause inside a JOIN statement - however, using a WITH allows us to reduce the complexity of the query
Let us consider the data base of an organisation, where we have a table ‘employee’ and a table ‘department’.
Lets find out the department of the top 3 highly paid employees.
Note that the department details are not mentioned in the table ‘employee’.
Below is query to find out the department of the top 3 highly paid employees
WITH top_employee AS( -- This table has only 2 columns - 'name' and 'emp_id'
SELECT name,emp_id
FROM employee
ORDER BY salary DESC
LIMIT 3 -- This table has only 3 rows - highest paid employees
)
SELECT top_employee.name,department.dept_name
FROM top_employee
JOIN department
ON top_employee.emp_id=department.emp_id;