Most efficient way to use calculated value in multiple clauses without selecting it?-CodePudding

What is the most efficient way to use a calculated value in multiple clauses without selecting it?

See (fictional) example below.

One approach is to calculate the value twice, but that feels unclean and inefficient:

SELECT
  price,
  sale_price
FROM products
WHERE (price - sale_price) > 10
ORDER BY (price - sale_price) DESC;

Another approach is to use a join, but that is quite slow:

SELECT
  price,
  sale_price,
FROM products
INNER JOIN (
  SELECT
    id,
    (price - sale_price) AS discount
  FROM products
) AS p ON products.id = p.id
WHERE p.discount > 10
ORDER BY p.discount DESC;

CodePudding user response：

Your last query is really not so bad :

SELECT price, sale_price, price - sale_price AS discount
FROM products
WHERE (price - sale_price) > 10
ORDER BY discount DESC

An alternative solution can be :

SELECT price, sale_price, discount
FROM products
CROSS JOIN LATERAL (price - sale_price) AS discount
WHERE discount > 10
ORDER BY discount DESC

but I don't really see the advantage. You can compare both solutions performances with EXPLAIN ANALYSE and share the results.

CodePudding user response：

Use a derived table:

select *
from (
  SELECT price,
         sale_price,
         (price - sale_price) AS discount
  FROM products
) t
WHERE discount > 10
ORDER BY discount DESC;