Geek asks and answers

Using a Common Table expression for easy selection

I noticed in our project the following use of the Common Table expression for a simple selection from a view:

WITH CTE_View_Alias AS
(
  SELECT 
    Id,
    Column1,
    Column2
  FROM VW_View
),
CTE_Foo AS
(
  SELECT
    B.Bar_Id,
    V.Column1
  FROM VW_Bar B
  INNER JOIN CTE_View_Alias V
    ON B.View_Id = V.Id
)

CTE is only used elsewhere, another CTE. Columns in CTE are subsets of view columns.

Is there any use to using this? Why not directly refer to the view? Unfortunately, the creator of these SQL queries no longer works with the company. For me, this reduces readability, but I do not want to eliminate it if there is a reason why my inexperience prevents me from seeing.

+3
source share
1 answer

There is no difference between:

WITH CTE_View_Alias AS (
 SELECT v.id,
        v.column1,
        v.column2
   FROM VW_View v)
SELECT t.*
  FROM CTE_View_Alias t

... and:

SELECT v.id,
       v.column1,
       v.column2
  FROM VW_View v

It is plausible that this is also not necessary, but it is not a question ...

:

 SELECT x.*
   FROM (SELECT v.id,
                v.column1,
                v.column2
           FROM VW_View v) x

CTE - , , .

, CTE. , , CTE - . , ...

, /, .

+2

More articles:


All Articles