Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

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,735 Points
Posted by Brendan Whiting
.a{fill-rule:evenodd;}techdegree seal-36
Brendan Whiting
Front End Web Development Techdegree Graduate 84,735 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
229,732 Points
Steven Parker
Steven Parker
229,732 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.

Mohammed Ismail
Mohammed Ismail
7,190 Points
Mohammed Ismail
Mohammed Ismail
7,190 Points

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.