Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases SQL Reporting by Example Day 3: Getting Good at Grouping Janis' Schedule

Brendan Whiting
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Brendan Whiting
Front End Web Development Techdegree Graduate 84,696 Points

Why is it called a 'common table expression'?

WITH SUBJECT_COUNT AS (
  SELECT COUNT(*) AS COUNT, SUBJECTS.NAME FROM SCHEDULE
  JOIN CLASSES ON SCHEDULE.CLASS_ID = CLASSES.ID
  JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID
  GROUP BY SUBJECTS.NAME
)

SELECT MIN(COUNT), NAME FROM SUBJECT_COUNT;

I just learned about common table expressions like this one. They seem really powerful and interesting but I'm wondering, why is it called that? What does 'common' mean in this context?

2 Answers

Steven Parker
Steven Parker
215,939 Points

I don't have an official reference, but I've always accepted the "common" to mean "common to the entire query". In this simple example, a derived table would serve just as well. But the situations where I would use one of these would be where I needed to reference the same data set more than once in the query. That's where these are particularly useful and help to keep the query compact.

Hi Brendan,

Please check out the below notes:

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.