Replacing CTEs with nested queries-CodePudding

There is such a table:

CREATE TABLE Pilot (
   EmployeeID smallserial PRIMARY KEY,
   Airline char(14),
   Name char(14),
   Salary real
) ;

INSERT INTO Pilot(Airline, Name, Salary) VALUES 
('Airbus 380', 'Kim', '60000'),
('Boeing', 'Laura', '20000'),
('Airbus 380', 'Will', '80050'),
('Airbus 380', 'Warren', '80780'),
('Boeing', 'Smith', '25000'),
('Airbus 380', 'Katy', '78000')

Task: Find all the airlines where the total salary of all pilots in that airline is more than the average of total salary of all pilots in the database.

I accomplished this task using WITH:

WITH totalSalary(Airline, total) as
(SELECT Airline, sum(Salary)
FROM Pilot
GROUP BY Airline),
airlineAverage(avgSalary) as 
(SELECT avg(Salary)
FROM Pilot )
SELECT Airline
FROM totalSalary, airlineAverage
WHERE totalSalary.total > airlineAverage.avgSalary;

Can this task be accomplished with subqueries or some other way?

CodePudding user response：

You can do this:

SELECT DISTINCT Airline
FROM (
   SELECT 
      Airline,
      SUM(Salary) OVER(PARTITION BY Airline) as s,
      AVG(Salary) OVER(PARTITION BY NULL) as a
   FROM Pilot
)x
WHERE s>a

see DBFIDDLE

CodePudding user response：

You can simplify the query:

with airlines as (
select distinct airline,sum(salary) over (partition by airline) as sum_salary, avg(salary) over() as avg_salary
from pilot
)
select * from airlines where sum_salary >= avg_salary;

Result here

Maybe there's another way...

CodePudding user response：

You can always rewrite a CTE to a subquery:

WITH x AS (SELECT ...)
SELECT ... FROM x;

is the same as

SELECT ...
FROM (SELECT ...) AS x;

It is largely a matter of taste, but some differences apply:

Before PostgreSQL v12, CTEs were always materialized, that is planned as written, while subqueries were “merged” into the main query and the whole thing was optimized. Since v12 that also happens with CTEs unless you use the keyword MATERIALIZE.
A CTE can be used several times in the main query.
A CTE can contain DML statements, while a subquery cannot.