Does T-SQL offer a simple, not over-engineered way to reduce code duplication of subqueries? (No temp tables, etc.)

Question

This seems to be a major issue with SQL in general:

SELECT
    A, B, C,
    ...
    X, Y,
    (
        SELECT TOP 1
            b.R
        FROM (
            SomeHugeSubqueryThatInclduesAWhereClause
        ) b
        ORDER BY
            b.R
    ) AS Z
FROM (
    AlmostTheSameSubqueryThatIncludesAnOnlySlightlyDifferentWhereClause
) a

The problem with using subqueries like this is code duplication. There are a few workarounds to this, including things like temp tables, stored procedures, etc.

At least one issue with those workarounds is that they're relatively involved for something that more general-purpose languages would only need a single line of code for. Other languages allow you to just add a variable real fast and keep referencing it, even if the object that variable's referencing had a deferred-execution iterator or something.

Temp tables might contend a little with the idea of deferred execution. Views, stored procedures, and functions would add more to the DB schema and make it more complicated to deploy and update. All of the above add several lines of boilerplate code and just feel like overengineering for something simple.

So the question is...Is there a good, general-purpose way to avoid code duplication in cases like this, without having something that feels over-engineered and adds several lines on its own?

Yes. It is called a common table expression (CTE) introduced with the `WITH` keyword. — Gordon Linoff, Dec 27 '17 at 14:59
CTE could be used only in ONE select that follows it. If you need to reuse the same query in more SELECTs you can use view to save the code once, or TVF (inline function) that accepts parameter that you can use in your WHERE clause — sepupic, Dec 27 '17 at 15:03
@sepupic Just to clarify, there can be multiple CTEs with nested references, but the final result is restricted to a single, possibly complex, statement as demonstrated in [this](https://stackoverflow.com/a/2140764/92546) answer. — HABO, Dec 27 '17 at 15:10
You might find that factoring this out into an inline function is beneficial anyway - if you unit test your SQL. — Martin Smith, Dec 27 '17 at 15:12
@HABO that is exactly I was talking about; I was not talking about nested CTE, but every CTE, even if reused in many others CTE, is a part of one single SELECT — sepupic, Dec 27 '17 at 15:27

score 5 · Accepted Answer · answered Dec 27 '17 at 15:01

You can do this with a common table expression (CTE). It looks something like this:

with s as (
      <some huge subquery>
     )
select . . .
       (select top 1 b.r
        from s b
        order by b.r
       ) AS Z
from (select s.*
      from s
      where . . .
     ) a;

However, you can probably do what you want just with window functions:

select s.*
from (select s.*, min(b.r) over () as min_r
      from s
     ) s
where . . .;

Does T-SQL offer a simple, not over-engineered way to reduce code duplication of subqueries? (No temp tables, etc.)

1 Answers1