VIEW vs CTE in SQL, difference and use cases

In this post, I will clearly explain all about View and CTEs (Common Table Expressions) to help you fully understand the difference and use cases for each one.

1. VIEW

A view is a virtual table and that is not part of the physical schema.

• A view doesn’t store the output of a particular query — it stores the query itself. That’s why it doesn’t take up any storage except for the query statement, which is minimal.
• A view can be queried like a regular database table.
• Data is aggregated from data in tables.

Why use VIEW?

• It saves time for you when you do not need to retype common queries or alter schemas.
• Views can be used to hide the complexity of the database. When you just want to show some necessary information, a View is enough.
• Security reasons. While you have sensitive data like health records, salary, or credit card numbers… you do not want users who are not authorized to view them. You can remove permission to the database and just keep the read permission on the View — which does not include sensitive information.

CREATE VIEW Syntax:

CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

Update a View:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

Delete a View:

DROP VIEW view_name;

Show all the views in the database (in PostgreSQL):

SELECT * FROM INFORMATION_SCHEMA.VIEWS;

2. CTEs

CTEs or Common Table Expressions are a special type of subquery that is declared before the main query. It also could be understood as a temporary table.

Why use CTEs?

• The CTE runs only once, then is stored in memory for the duration of the query, so it will improve the amount of time it takes to run your query.
• It’s an excellent tool for organizing long and complex queries. You can declare as many CTEs as you need, one after another.
• A CTE can reference other CTEs, your third CTE can retrieve information from the first and second CTE like tables in the database.
• A CTE can reference itself (SELF JOIN) in a special kind of table called recursive CTE.

Instead of wrapping subqueries inside FROM or WHERE statement, you name it using WITH statement. And then, named and referenced it later in FROM statement:

WITH cte1 AS (
SELECT column1, column2, …
FROM table_name
WHERE condition)

cte2 AS (
SELECT column1, column2, …
FROM table_name
WHERE condition)

SELECT COUNT(column1) AS num_col1, AVG(column2) AS avg_col2
FROM cte1 JOIN cte2 ON…;

3. Choose CTE or VIEW?

Despite the difference, CTE and VIEW seem to perform very similarly. So when to use CTE and when to create a VIEW? Let's see the table below:

I hope this will clear your mind about CTE and VIEW. Who knows this will be your next interview question, be prepared and good luck!