[SQL] Should we use CTE (Common Table Expression) or SubQuery?
When we were handling Acumatica/Dynamics 365 Business Central join project, some developer just asked should we use CTE or Subquery to achive one of query in SQL procedure. Both looks similar and they can achieve similar things.
Here is example how to use Subquery
Insert into #PurchLine
SELECT [No_],[Quantity],[Expected Receipt Date] FROM (
SELECT
[No_]
,[Type]
,[Quantity]
,[Outstanding Quantity]
,[Expected Receipt Date]
,ROW_NUMBER() OVER (
PARTITION BY [No_]
ORDER BY [Expected Receipt Date]
) AS [RANK]
FROM [SME].[dbo].[MyPurchLine]
WHERE [Type]= 2 and [No_] <> ”
) PurchGroup
WHERE PurchGroup.[RANK] = 1
And Here is example how to use CTE
;WITH PurchGroup AS (
SELECT
[No_]
,[Type]
,[Quantity]
,[Outstanding Quantity]
,[Expected Receipt Date]
,ROW_NUMBER() OVER (
PARTITION BY [No_]
ORDER BY [Expected Receipt Date]
) AS [RANK]
FROM [SME].[dbo].[MyPurchLine]
WHERE [Type]= 2 and [No_] <> ”
)
Insert into #PurchLine
SELECT [No_],[Quantity],[Expected Receipt Date] from PurchGroup
WHERE PurchGroup.[RANK] = 1
Okay, then why we bother?
Here is what I’ve found so far.
- If you want to query to be used in WHERE Claus, use Subquery
- If you want to reuse the query you have created, use CTE
- If you want to have recursive query, you might lose some of your hairs, then use CTE
- If you want to create each colume came from query, then use subquey
We don’t have simple right and wrong answer when we choose Subquery or CTE (or sometimes Temporary table). But we are lucky that we have many tools in SQL world and we can use properly when time comes.
