Learn SQL Database Programming

Non-recursive CTE with the SELECT statement

To help you understand a good use case for a CTE, let's work with a query from earlier in this chapter that uses a derived table:

USE lahmansbaseballdb; SELECT ROUND(AVG(average_salary), 0) AS average_of_all_teams_salariesFROM (SELECT AVG(salary) average_salary FROM salaries GROUP BY teamid) AS team_salaryWHERE team_salary.average_salary > 2000000;

The preceding query will be rewritten into the following CTE:

USE lahmansbaseballdb;WITH avgsalarycte AS(SELECT AVG(salary) AS average_salaryFROM salariesGROUP BY teamid)SELECT ROUND(AVG(average_salary), 0) AS average_of_all_teams_salariesFROM avgsalarycteWHERE average_salary > 2000000;

The CTE query will give you the same results as the derived table ...