locked
Use of the WITH statement RRS feed

  • Question

  • Is there any reason to use the WITH statement rather than putting a SELECT in a JOIN? Does one method perform better or is it just a matter of preference?
    Thursday, June 18, 2015 8:29 PM

Answers

  • You mean using a Common Table Expression (i.e. WITH) or a derived table like:

    SELECT ...
    FROM   tbl
    JOIN   (SELECT ...)

    Yes, that is largely a matter of preference. A CTE has a name so it can be referred to in several places in the query, but SQL Server will compute each time per appearance.

    If you have a recursive CTE, that cannot be replaced with a derived table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Patrick Hurst Thursday, June 18, 2015 10:12 PM
    • Marked as answer by Adam Quark Friday, June 19, 2015 5:52 PM
    Thursday, June 18, 2015 10:05 PM

All replies

  • You mean using a Common Table Expression (i.e. WITH) or a derived table like:

    SELECT ...
    FROM   tbl
    JOIN   (SELECT ...)

    Yes, that is largely a matter of preference. A CTE has a name so it can be referred to in several places in the query, but SQL Server will compute each time per appearance.

    If you have a recursive CTE, that cannot be replaced with a derived table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Patrick Hurst Thursday, June 18, 2015 10:12 PM
    • Marked as answer by Adam Quark Friday, June 19, 2015 5:52 PM
    Thursday, June 18, 2015 10:05 PM
  • You mean using a Common Table Expression (i.e. WITH) or a derived table like:

    SELECT ...
    FROM   tbl
    JOIN   (SELECT ...)

    Yes, that is largely a matter of preference. A CTE has a name so it can be referred to in several places in the query, but SQL Server will compute each time per appearance.

    If you have a recursive CTE, that cannot be replaced with a derived table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Indeed.

    Although, it looks like I've found a reason to not use a CTE.

    Seems, when using a CTE, you're not allowed to have anything preceeding the WITH, like DECLARES and what not. But, a derived table has no problem.

    Anyway, I think my question's been answered. Thanks, Erland!

    Friday, June 19, 2015 5:54 PM
  • Seems, when using a CTE, you're not allowed to have anything preceeding the WITH, like DECLARES and what not. But, a derived table has no problem.

    You can have anything before the WITH. But the previous statement must be terminated with a semi-colon. Since semi-colons is something which has been slapped on T-SQL to conform with the ANSI standard, not all people use it consistently (and that includes yours truly). For this reason many people put the semicolon in front of the WITH instead.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 19, 2015 6:49 PM