What is the most optimal approach for a rate lookup in single SQL select query?-CodePudding

I am working on a platform that executes SQL select statements to get data for reporting.

I need to write a query that returns all columns from table A, plus a few calculated columns.
The calculated columns are multiplications with a single column from table B, repeated few times for different B rows, identified with a constant, i.e.

SELECT 
  A.Column1, 
  A.Column2, 
  A.Column1 * B1.Rate as Column1_Rate1,
  A.Column2 * B1.Rate as Column2_Rate1,
  A.Column1 * B2.Rate as Column1_Rate2,
  A.Column2 * B2.Rate as Column2_Rate2
FROM TableA A
LEFT JOIN TabelB B1 on B1.ID = 'ID1'
LEFT JOIN TabelB B2 on B2.ID = 'ID2'

My question is - what would be the most optimal way to write such a query, considering that:
I am working with MSSQL 2019.
I cannot use a stored procedure (if I could, I would move rate lookup into a separate statement, which I think would be the most optimal).
The query will become a sub-query of another select statement, that will only pick a subset of columns from it, e.g.

SELECT Column1, Column1_Rate1 FROM (^ABOVE QUERY^)

CodePudding user response：

I can advice next approach: Because table of rates not linked to data table you can grab rates by one query and use cross join after that:

SELECT 
  A.Column1, 
  A.Column2, 
  A.Column1 * Rate1 as Column1_Rate1,
  A.Column2 * Rate1 as Column2_Rate1,
  A.Column1 * Rate2 as Column1_Rate2,
  A.Column2 * Rate2 as Column2_Rate2
FROM TableA A
CROSS JOIN (
  SELECT 
      MIN(CASE WHEN ID = 'ID1' THEN Rate END) Rate1,
      MIN(CASE WHEN ID = 'ID2' THEN Rate END) Rate2
  FROM TableB WHERE ID IN ('ID1', 'ID2')
) Rates

Test MS SQL 2019 queries online

or using CTE:

WITH Rates AS (
  SELECT 
      MIN(CASE WHEN ID = 'ID1' THEN Rate END) Rate1,
      MIN(CASE WHEN ID = 'ID2' THEN Rate END) Rate2
  FROM TableB WHERE ID IN ('ID1', 'ID2')
) SELECT 
  A.Column1, 
  A.Column2, 
  A.Column1 * Rate1 as Column1_Rate1,
  A.Column2 * Rate1 as Column2_Rate1,
  A.Column1 * Rate2 as Column1_Rate2,
  A.Column2 * Rate2 as Column2_Rate2
FROM Rates, TableA A;