Should SQL CTE's hold constant values?-CodePudding

I came across an interesting piece of SQL code in a view today where a CTE was being used to hold constant values used within sub-queries. It made raise an eyebrow as I've never seen this practice before, though I can understand the thought process behind wanting to reduce the duplicate occurrences of a string value . I'm more of a .Net guy, so I would love to hear thoughts from some SQL folks on whether this pattern is a good or bad practice.

WITH myConstants AS (
    SELECT 'myValue' AS myValue
)
SELECT
.......
    (SELECT .......
    WHERE x.myValue = mc.myValue
    ) AS mySubQuery
FROM 
.......
INNER JOIN myConstants mc ON 1=1

CodePudding user response：

You could use a CTE to store a constant in this way for scoping as a kind of "local variable" (though I've never seen one used for this purpose). After you've completed a statement using the CTE that holds the constant, you can't access it later within the same batch (unless your statement stored it somewhere else). A variable on the other hand, you could access repeatedly throughout the same batch.

e.g.

DECLARE @myVar VARCHAR(20) = 'test';

WITH myConstants AS (
    SELECT 'myValue' AS myValue
)
SELECT myValue
FROM myConstants

-- can't access myConstants CTE or its content at this point

-- but can access the variable within the same batch...
SELECT @myVar AS MyVar 

-- ...as many times as needed
SELECT @myVar AS MyVar 
GO

CodePudding user response：

I use a CTE for constant values all the time. And YES, you can use the CTE values just like you would a variable/parameter. Note this query:

WITH x(c1,c2,c3) AS (SELECT 1,2,3)
SELECT x.c2 AS xyz FROM x
UNION ALL
SELECT x.c1 FROM x
EXCEPT
SELECT x.c3 FROM x;

Returns:

xyz
----
2
1

The nuance is that you must include a reference to the CTE each time you do. Each time I "call" a "constant" I must include FROM x.

In short, a CTE is simply cleaner syntax for a subquery. The main difference between the two is that CTEs can be recursive, subqueries can't be. Subqueries, on the other hand, can be correlated. Note these three queries that produce the exact same results and exact same execution plan:

DECLARE @sometable TABLE (SomeId INT);
INSERT @sometable VALUES(1),(1),(3),(4),(5);

-- CTE
WITH myConstants(c1,c2,c3) AS 
(
  SELECT 1, 2, 3
)
SELECT     t.SomeId, Total = COUNT(*) 
FROM       @sometable       AS t
CROSS JOIN myConstants AS m
WHERE      t.SomeId IN (c1,c2,c3)
GROUP BY   t.SomeId;

-- Subquery
SELECT t.SomeId, Total = COUNT(*)
FROM @sometable AS t
CROSS JOIN (SELECT 1,2,3) AS myConstants(c1,c2,c3)
WHERE      t.SomeId IN (c1,c2,c3)
GROUP BY   t.SomeId;

-- VALUES Constructor
SELECT t.SomeId, Total = COUNT(*)
FROM @sometable AS t
CROSS JOIN (VALUES(1,2,3)) AS myConstants(c1,c2,c3)
WHERE      t.SomeId IN (c1,c2,c3)
GROUP BY   t.SomeId;

The benefit of a CTE vs a subquery is when you need to perform nesting. Consider this query (which uses the same temp table from above):

SELECT t.SomeId
FROM 
(
  SELECT t.SomeId
  FROM   @sometable AS t
  WHERE  t.SomeId < 10
) AS logic1
JOIN @sometable AS t
 ON  logic1.SomeId = t.SomeId
UNION ALL
SELECT TOP(2) logic3.SomeId
FROM
(
  SELECT logic2.SomeId 
  FROM
  (
    SELECT t.SomeId
    FROM 
    (
      SELECT t.SomeId
      FROM   @sometable AS t
      WHERE  t.SomeId < 10
    ) AS logic1
    JOIN @sometable AS t
     ON  logic1.SomeId = t.SomeId
  ) AS logic2
) AS logic3;

This can be simplified like so:

WITH
logic1 AS
(
  SELECT t.SomeId
  FROM   @sometable AS t
  WHERE  t.SomeId < 10
),
logic2 AS
(
  SELECT t.SomeId
  FROM   logic1     AS m
  JOIN   @sometable AS t
   ON    t.SomeId = m.SomeId
),
logic3 AS
(
  SELECT TOP(2) m.SomeId
  FROM logic2 AS m
)
SELECT m.SomeId
FROM   logic2 AS m
UNION ALL
SELECT m.SomeId
FROM   logic3 AS m;

Again, both return the exact same results and produce the exact same execution plan.