DEV Community

Pavol Z. Kutaj
Pavol Z. Kutaj

Posted on

1

Explaining (the need for) SQL's Common Table Expressions (CTE)

usecase

The concern is documenting the use of COMMON TABLE EXPRESSIONS as used repeatedly in Redash when creating monitoring queries / reports. Also, I ran into the order of SQL operations learning that you cannot filter a derived column in the same statement where it is defined.

1. what is CTE

  • A CTE (Common Table Expression) is TEMPORARY RESULT SET
  • You can reference within another SELECT, INSERT, UPDATE, or DELETE statement
  • i.e. you can build it and use in later query
  • used in more complex queries
  • they are EXPRESSIONS, i.e. they always return a value

2. syntax

Image description

Image description

Image description

3. benefits

  • easy to learn
  • code readibility
  • named query gives you info about query results
  • readable SQL is a big help to others and to you after 2 months
  • modularity
  • copy-pastable, re-usable
  • CTEs can be pasted together
  • better matches how you think about data analysis
  • Common Table Expressions (CTE) are often used to reduce the complexity of SQL queries.
  • Sometimes, CTEs are not just a refactoring technique; they are necessary if you want to filter on a derived column.

Sometimes, CTE is a necessity - not just a refactoring technique

select player_name,
       year,
       case when year ='SR' then 'yes'
       else null
       end as "is_a_senior"
from benn.college_football_players
where is_a_senior = "yes"
Enter fullscreen mode Exit fullscreen mode
  • That returnes an error column "is_a_senior" does not exist occurs when referencing a derived column in the WHERE clause.
  • It is because the order of SQL query processing:
    1. FROM
    2. WHERE
    3. GROUP BY
    4. HAVING
    5. SELECT
    6. ORDER BY
    7. LIMIT/OFFSET
  • The WHERE clause is evaluated before the SELECT clause, causing the derived column alias (is_a_senior) to be unavailable in the WHERE clause.
  • The simple solution using Common Table Expressions (CTE) (there may be others) is as follows:
WITH player_data AS (
    SELECT player_name,
           year,
           CASE WHEN year = 'SR' THEN 'yes'
                ELSE null
           END AS is_a_senior
    FROM benn.college_football_players
)
SELECT player_name,
       year,
       is_a_senior
FROM player_data
WHERE is_a_senior = 'yes';
Enter fullscreen mode Exit fullscreen mode

4. sources

https://mode.com/sql-tutorial/sql-case

Heroku

Simplify your DevOps and maximize your time.

Since 2007, Heroku has been the go-to platform for developers as it monitors uptime, performance, and infrastructure concerns, allowing you to focus on writing code.

Learn More

Top comments (0)

Billboard image

Try REST API Generation for MS SQL Server.

DreamFactory generates live REST APIs from database schemas with standardized endpoints for tables, views, and procedures in OpenAPI format. We support on-prem deployment with firewall security and include RBAC for secure, granular security controls.

See more!

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay