What’s the Difference Between SQL CTEs and Views?

SQL views vs. CTEs: What do they do? How are they different? Which one should you use and when? Get your answers here!

In SQL, both CTEs (common table expressions) and views help organize your queries, leading to cleaner and easier-to-follow code. However, there are some important differences between them. This article will walk you through several examples of CTEs and views and explain when to use each one.

What Are Common Table Expressions (CTEs) in SQL?

As you may recall, common table expressions are named temporary result sets that are created using simple SQL statements and then referenced within SELECT, INSERT, UPDATE, or DELETE statements.

For example, let’s say we have a table called top_apps with rating information for top applications across different categories:

We also have the google_apps table, which has the same information for several Google applications:

We want to compare the performance of Google apps to that of the top-performing applications in the corresponding categories. Specifically, we want information on the maximum rating in each category to be shown next to the rating of the Google app from the same category.

Here’s a query that accomplishes this goal using a CTE:

WITH top_apps_max AS (
	SELECT category, MAX(rating) AS max_rating
	FROM top_apps
	GROUP BY category)
SELECT ga.name, ga.category, ga.rating, tam.max_rating
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

The query starts by creating a temporary result set called top_apps_max. This result set is derived from the top_apps table and includes the list of categories together with the corresponding maximum ratings. Then, in the main SELECT statement, we join this temporary result set with the google_apps table using the common category column. Here’s the result:

You could get the same output using a subquery instead of a CTE. However, compared to subqueries, using a SQL CTE results in cleaner and easier-to-follow code that you can read from top to bottom: you first create a temporary result set with a specific name that is used later in the query to reference that result set.

Note that CTE exists in memory only while the query is running. After the query is run, the CTE is discarded; it cannot be used for the next SQL query unless we define it again. Still, the same CTE might be referenced several times in the main query and any subqueries.

You can learn more about SQL CTEs in this comprehensive introductory guide to common table expressions. And if you are interested in practicing CTEs with real-world examples, check out our interactive course on common table expressions.

In everyday speech, CTEs are sometimes called inline views. So, let’s recall what a view is and how it differs from a CTE.

What Is a View in SQL?

A view is a stored SQL query that is executed each time you reference it in another query. Note that a view doesn’t store the output of a particular query – it stores the query itself.

Let’s see how this works. We’ll use a similar example, but this time we’ll use a view instead of a CTE.

We’ll start by creating the top_apps_max view with the CREATE VIEW keyword, followed by the SELECT statement:

CREATE VIEW top_apps_max AS
SELECT category, MAX(rating) AS max_rating, MAX(reviews) AS max_num_reviews
FROM top_apps
GROUP BY category;

As you see, the SELECT statement is very similar to the one we used with the CTE. It could be absolutely the same, but we’ve changed the scope: Now we want to see the maximum number of reviews in addition to the maximum rating for each category (just to have more data to work with).

So, now we have a stored SQL query called top_apps_max. It’s time to use it!

Let’s start by replicating our first example using a view instead of a CTE. However, this time:

• We don’t need to create a CTE at the beginning of the query, as we already have the stored view top_apps_max.
• We simply join the google_apps table with the top_apps_max view on the category column and list the columns we want to see in the output:

SELECT ga.name, ga.category, ga.rating, tam.max_rating
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

The result of this query will be the same as in our first example:

So, what’s the difference?

First of all, you can use the same view in other queries without defining it again. For example, the SQL query below references the same top_apps_max view; this time, it’s used to compare the number of reviews rather than the rating of different applications:

SELECT ga.name, ga.category, ga.reviews, tam.max_num_reviews
FROM google_apps ga
JOIN top_apps_max tam
ON ga.category = tam.category;

Furthermore, you can use a CTE when defining a view. Let’s say we also want to see the name of the top app in each category (i.e. the app with the highest number of reviews). One way we can do this is to create a view that outputs the name, category, and number of reviews for the top application in each category:

CREATE VIEW top_app_per_category AS
WITH top_app_max_reviews AS (
    SELECT category, MAX(reviews) AS max_num_reviews
    FROM top_apps
    GROUP BY category)
SELECT ta.name, ta.category, ta.reviews
FROM top_apps ta
JOIN top_app_max_reviews tamr
ON ta.reviews = tamr.max_num_reviews;

As you see, the query stored in this view includes a common table expression that outputs the maximum number of reviews for each category. Then, in the main query of the top_app_per_category view, we join this CTE with the top_apps table to get the name of the application with the highest number of reviews in each category.

Now we can reference this view in another query that returns Google applications together with the name of the top application in the corresponding category and its number of reviews:

SELECT ga.name, ga.category, ga.reviews, top.name AS top_app, top.reviews AS top_app_reviews
FROM google_apps ga
JOIN top_app_per_category top
ON ga.category = top.category;

The key thing to remember about SQL views is that, in contrast to a CTE, a view is a physical object in a database and is stored on a disk. However, views store the query only, not the data returned by the query. The data is computed each time you reference the view in your query.

Interested to learn more about SQL views? Here’s a great article explaining SQL views using multiple examples and illustrations. Also, remember to practice views with our interactive Working with Views course.

SQL CTE vs. View: When to Use Each One

Although there are some differences between them, common table expressions and views seem to perform very similarly. So, when should you use each one?

• Ad-hoc queries. For queries that are referenced occasionally (or just once), it’s usually better to use a CTE. If you need the query again, you can just copy the CTE and modify it if necessary.
• Frequently used queries. If you tend to reference the same query often, creating a corresponding view is a good idea. However, you’ll need create view permission in your database to create a view.
• Access management. A view might be used to restrict particular users’ database access while still allowing them to get the information they need. You can give users access to specific views that query the data they’re allowed to see without exposing the whole database. In such a case, a view provides an additional access layer.

Let’s Practice SQL CTEs and Views!

Now that you have a basic understanding of SQL CTEs and views, you are ready to start using them in your queries! Practicing SQL queries is the best way to understand how CTEs work and how views save time on retyping and running queries.

LearnSQL.com has developed several courses that cover these topics in depth. First of all, check out the Recursive Queries course, which will guide you from simple CTEs through nested CTEs and on to the most challenging recursive CTEs. 114 interactive exercises will help you master common table expressions in the most efficient way.

For those interested in learning more about SQL views, we’ve prepared the Working with Views course. This is an advanced course that will teach you how to create, modify, and remove views in SQL Server, MySQL, Oracle, and PostgreSQL.

Thanks for reading, and happy learning!