Calculate difference of multiple highest and lowest column values

Postgres 13 adds the WITH TIES clause to include all peers of the nth row:

• Greater than or equal to ALL() and equal to MAX() speed

If you have an index on salary, this will be as fast as it gets. Much faster than involving window functions:

SELECT COALESCE(sum(salary), 0) AS diff
FROM  (
   (  -- parentheses required
   SELECT salary
   FROM   tbl
   ORDER  BY salary DESC
   FETCH  FIRST 1 ROWS WITH TIES
   )
   UNION ALL
   (
   SELECT salary * -1
   FROM   tbl
   ORDER  BY salary
   FETCH  FIRST 1 ROWS WITH TIES
   )
   ) sub;

db<>fiddle here

Postgres can take the first and last values from an index on (salary) directly. Quasi-instantaneous result, no matter how big the table might be.

COALESCE() to get 0 instead of NULL when the table is empty.

Why the extra parentheses? The manual:

(ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

See:

• Create a unique index on a non-unique column
• Combining 3 SELECT statements to output 1 table

This is assuming salary is NOT NULL, else append NULLS LAST to the descending order. See:

• Sort by column ASC, but NULL values first?