Sunday, June 27, 2021

What is Common Table Expression (CTE) in Snowflake?

    Are you working on Snowflake technology and would like to understand Common Table Expression also known as CTE? If so, then you reached the right place. In this article, we will understand what is Common Table Expression. We will also explore how to use CTE in queries.

A) What is Common Table Expression i.e CTE?

      A Common Table Expression (CTE) is a named subquery. It is defined in a WITH Clause. It is equivalent to a temporary view.

      The  CTE Contains an optional list of columns and a SELECT Statment. The output of CTE is a table with a column defined.

B) What is the syntax for CTE?

     The syntax for CTE is as below


            TEMP_CTE (COL_1 ,COL_2) AS

              (SELECT col_nm1 , col_nm2 From TABLE)


C) What is recursive CTE?

      A recursive CTE is a CTE that references itself. In a recursive CTE, we can join Table to itself as many times as needed.

D) Benefits of CTE?

      1) CTE increases the modularity of the SQL programs

       2) CTE is helpful for simplified maintenance

       3) Recursive CTE can be used to process hierarchical data in the table.

E) Naming conventions for CTE?

      We need to avoid CTE names that match with database views or table the reason for this is - if we define query with CTE names then CTE takes precedence over table or view names which will produce unexpected results.

         For more details about snowflake refer to this video  

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

What is difference between On-premise Informatica MDM and Cloud Informatica MDM and SAAS Informatica MDM?

On-premise, cloud, and SaaS Informatica MDM are all master data management (MDM) solutions that help organizations manage the consistency an...