0

Assume you are creating some query using tables A and B, which are inner joined on COL_ID:

SELECT A.*, B.* 
FROM A INNER JOIN B ON (A.COL_ID = B.COL_ID);

Let's say you want to transform a lot of the columns from table B, and as the query is going to have lots and lots of columns, you decide to move the transformation logic into a CTE, i.e:

WITH cte_b AS (
    SELECT (CAST(B.COL1 AS INT) AS COL1_INT,
           .....
    FROM B
)
SELECT A.*, cte_b.* 
FROM A INNER JOIN cte_b ON (A.COL_ID = cte_b.COL_ID);

As opposed to including any transformations in the main query:

SELECT A.*, 
       B.*,
       (CAST(B.COL1 AS INT) AS COL1_INT,
           .....
FROM A INNER JOIN B ON (A.COL_ID = B.COL_ID);

Between the 2 options - is there a significant peformance difference?

cardycakes
  • 431
  • 1
  • 4
  • 12
  • 2
    Have you run your horses and found out? You are in a far better position to check than us. – Thom A Sep 12 '22 at 12:14
  • The query is more from a theory POV - i.e. are the same opertaions peformed whether it's a join or a cte? Are more rows cached/loaded into memory with the cte than the inner join, etc? – cardycakes Sep 12 '22 at 12:16
  • 2
    A CTE is an *expression*, as it's name suggests (Common Table **Expression**); it isn't "loaded into memory" first. – Thom A Sep 12 '22 at 12:17
  • Though for your second query the `CAST` may cause the query to be non-SARGable, so will likely be slower. – Thom A Sep 12 '22 at 12:18
  • 1
    A view, a derived table and a CTE are compiled in exactly the same way, so no it won't make a difference. Unless of course there is a *semantic* difference, such as if the join or where condition is on `COL1_INT` (it isn't in your case) – Charlieface Sep 12 '22 at 13:03
  • Thank you @Charlieface - exactly what I wanted to know. Do you want to put this as an answer? – cardycakes Sep 12 '22 at 14:58

0 Answers0