COMMON TABLE EXPRESSION

This explain to you the common table expression concept and show you how to use CTE for querying data from tables.

Common Table Expression (CTE) in SQL is like a temporary table that only exists while the query is running. It helps break down complex queries into smaller, more manageable parts. You can use it in SQL commands like SELECT, INSERT, UPDATE, or DELETE.

Here’s how a CTE works:

• Think of it like a temporary named result you create within a query. Once created, you can use this result just like a normal table in your query.
• Unlike a temporary table, a CTE disappears as soon as the query finishes.
• It’s more flexible than a derived table because you can use it multiple times in a query and it’s easier to read and understand.
• A special kind of CTE, called a recursive CTE, can refer to itself and is useful for hierarchical data (like company org charts).

WITH cte_name (column_list) AS (
    query
) 
SELECT * FROM cte_name;

Breaking down the syntax:

1. WITH cte_name (column_list) AS: You give your CTE a name (cte_name) and can optionally list its columns. If you don’t list them, the columns are automatically named based on the result of your query.
2. query: This is where you define the data inside your CTE. It’s just a normal SQL query that gets run first.
3. SELECT * FROM cte_name: Once you’ve defined the CTE, you can use it like a table to pull or modify data.

In short, a CTE helps simplify queries, making them easier to understand and reuse within a query.