Back to articles list January 14, 2021 - 7 minutes read What’s the Difference Between SQL CTEs and Views? Kateryna Koidan Kateryna is a data science writer from Kyiv, Ukraine. She worked for BNP Paribas, the leading European banking group, as an internal auditor for more than 6 years. More recently, she decided to pursue only the favorite part of her job—data analysis. Now she is continuing her self-education with deep-learning courses, enjoys coding for data analysis and visualization projects, and writes on the topics of data science and artificial intelligence. Kateryna is also a proud mother of two lovely toddlers, who make her life full of fun. Tags: sql learn sql CTE view 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: top_apps idnamecategoryratingreviews 1Messengercommunication4.275 645 262 2WhatsAppcommunication4.3126 283 877 3Zoomcommunication3.71 568 095 4Duolingoeducation4.610 261 344 5Udemyeducation4.4263 125 6Courseraeducation4.3119 751 7Spotifymusic4.521 001 626 8Shazammusic4.43 928 072 9Samsung Musicmusic4.4593 808 We also have the google_apps table, which has the same information for several Google applications: google_apps idnamecategoryratingreviews 201Google Meetcommunication3.4999 265 202Google Classroomeducation1.9886 558 203YouTube Musicmusic3.41 953 141 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: namecategoryratingmax_rating Google Meetcommunication3.44.3 Google Classroomeducation1.94.6 YouTube Musicmusic3.44.5 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: namecategoryratingmax_rating Google Meetcommunication3.44.3 Google Classroomeducation1.94.6 YouTube Musicmusic3.44.5 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; namecategoryreviewsmax_num_reviews Google Meetcommunication999 265126 283 877 Google Classroomeducation886 55810 261 344 YouTube Musicmusic1 953 14121 001 626 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; namecategoryreviewstop_apptop_app_reviews Google Meetcommunication999 265WhatsApp126 283 877 Google Classroomeducation886 558Duolingo10 261 344 YouTube Musicmusic1 953 141Spotify21 001 626 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! Tags: sql learn sql CTE view You may also like What Is a CTE? We explain what CTE is and how to use common table expressions in SQL scripts. Read more What is an SQL View? An SQL view allows you to not only store an SQL query but also use it as a table in other queries. Learn how to apply this helpful SQL feature! Read more Subquery vs. CTE: A SQL Primer SQL subqueries and CTEs seem similar, but even pros may not know all their quirks. Learn their similarities, differences, and best use cases. Read more Long SQL Query vs. Recursive SQL Query Explore recursive CTE hierarchy in SQL server and find out how to tune your SQL query performance. Read more How to Organize SQL Queries with CTEs Common table expressions (CTEs) allow you to structure and organize your SQL queries. It is a necessity when you begin to move deeper into SQL. Read more What Is Data Engineering? What’s data engineering and how does it fit in with data science and Big Data? Read this article to find out. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.