In the past when I was creating sql statements there was many times I would need to have more than one subquery that was just slightly different than the first. However, now there is a better way to reuse a subquery in T-SQL and that is using a common table expression (CTE).
A common table expression (CTE) is defined by:
WITH cte_name_here AS
(
cte_query_here
)
outer_sql_query_that_uses_cte_name_here
You can provide aliases for the results of your common table expression by putting the aliases in the CTE query or in the CTE definition.
WITH mycte (CustomerNumber, InvoiceYear) AS
(
SELECT CustomerId, YEAR(invoicedatepaid)
FROM Invoice
)
SELECT CustomerNumber, InvoiceYear
FROM mycte ;
OR
WITH mycte AS
(
SELECT CustomerId AS CustomerNumber, YEAR(invoicedatepaid) AS InvoiceYear
FROM Invoice
)
SELECT CustomerNumber, InvoiceYear
FROM mycte;
You can use variables with common table expressions just like you can with sub queries. The example below will filter invoices just for the month of March.
DECLARE @StartDate AS DATETIME = '3/1/2014',
@EndDate AS DATETIME = '4/1/2014';
WITH mycte (CustomerNumber, InvoiceYear) AS
(
SELECT CustomerId, YEAR(invoicedatepaid)
FROM Invoice
WHERE invoicedatepaid >= @StartDate
AND invoicedatepaid < @EndDate
)
SELECT CustomerNumber, InvoiceYear
FROM mycte ;
Unlike subqueries, you cannot nest common table expressions directly, but you can build upon a previous one in sequence by referring to the previous CTE.
WITH mycte1 (CustomerNumber, InvoiceYear, RetailAmount) AS
(
SELECT CustomerId, YEAR(invoicedatepaid), RetailAmount
FROM Invoice
),
mycte2 (InvoiceYear, YearlyAmount) AS
(
SELECT InvoiceYear, SUM(RetailAmount)
FROM mycte1
),
SELECT InvoiceYear, YearlyAmount
FROM mycte2 ;
The best feature of a CTE is that you can refer to the same one more than once.
WITH mycte (CustomerNumber, InvoiceYear, RetailAmount) AS
(
SELECT CustomerId, YEAR(invoicedatepaid), RetailAmount
FROM Invoice
)
SELECT tab1.InvoiceYear, tab2.RetailAmount
FROM mycte tab1
inner join mycte tab2
where tab1.CustomerId = tab2.CustomerId
and tab1.InvoiceYear = tab2.InvoiceYear;
The benefit is that you can define the recordset once and not need to copy and paste it. This will help with maintenance later.