CREATE VIEW syntax in SQL - Deep Dive

Overview - CREATE VIEW syntax

What is it?

A VIEW in SQL is like a saved query that you can treat as a virtual table. The CREATE VIEW syntax lets you define this saved query with a name, so you can use it repeatedly without rewriting the query. It does not store data itself but shows data from the underlying tables when you use it. This helps simplify complex queries and organize your database work.

Why it matters

Without views, you would have to write complex queries every time you want to see specific data. Views save time and reduce mistakes by letting you reuse queries easily. They also help protect sensitive data by showing only certain columns or rows to users. Without views, managing and securing data would be harder and more error-prone.

Where it fits

Before learning CREATE VIEW, you should understand basic SELECT queries and how tables work in SQL. After mastering views, you can learn about materialized views, stored procedures, and database security to control data access better.

Mental Model

Core Idea

A VIEW is a named saved query that acts like a virtual table, showing data dynamically from underlying tables whenever you use it.

Think of it like...

Think of a VIEW like a recipe card for a dish you like. Instead of cooking the dish every time from scratch, you just follow the recipe card. The card itself doesn't hold food, but it tells you exactly how to make the dish whenever you want.

┌─────────────┐       ┌─────────────┐
│  CREATE    │       │  SELECT     │
│  VIEW v1   │──────▶│  columns    │
│ AS query   │       │  FROM table │
└─────────────┘       └─────────────┘
       │                     │
       ▼                     ▼
┌─────────────────────────────────┐
│  v1 behaves like a virtual table │
│  showing data from the query    │
└─────────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic SELECT Queries

Concept: Learn how to retrieve data from tables using SELECT statements.

A SELECT query lets you choose columns and rows from a table. For example, SELECT name, age FROM employees; shows the name and age of all employees.

Result

A list of names and ages from the employees table.

Knowing how SELECT works is essential because views are built from SELECT queries.

2

Foundation

What Is a View in SQL?

Concept: Introduce the idea of a view as a virtual table based on a SELECT query.

A view is like a saved SELECT query with a name. When you query the view, it runs the saved SELECT and shows the results as if it were a table.

Result

You can use the view name in queries just like a table name.

Understanding that views do not store data but show live query results helps avoid confusion.

3

Intermediate

Basic CREATE VIEW Syntax

🤔Before reading on: do you think CREATE VIEW stores data or just saves a query? Commit to your answer.

Concept: Learn the syntax to create a simple view from a SELECT query.

The syntax is: CREATE VIEW view_name AS SELECT columns FROM table WHERE condition; For example, CREATE VIEW young_employees AS SELECT name, age FROM employees WHERE age < 30;

Result

A view named young_employees that shows only employees younger than 30.

Knowing the exact syntax lets you create reusable queries that simplify your work.

4

Intermediate

Using Views in Queries

🤔Before reading on: do you think you can join a view with a table in a query? Commit to your answer.

Concept: Learn how to use views just like tables in SELECT statements.

Once a view is created, you can query it like a table: SELECT * FROM young_employees; You can also join it with other tables: SELECT y.name, d.department FROM young_employees y JOIN departments d ON y.department_id = d.id;

Result

Results combining data from the view and other tables.

Understanding that views behave like tables in queries helps you integrate them smoothly into your database work.

5

Intermediate

Updating Data Through Views

🤔Before reading on: do you think you can update data through any view? Commit to your answer.

Concept: Learn when and how you can update data using views.

Some views allow updates if they are simple enough (like selecting from one table without joins). For example, UPDATE young_employees SET age = age + 1 WHERE name = 'Alice'; works if the view supports updates. Complex views with joins or aggregations usually do not allow updates.

Result

Data in the underlying table changes if the view supports updates.

Knowing the limits of updating through views prevents errors and confusion.

6

Advanced

Modifying Views with CREATE OR REPLACE

🤔Before reading on: do you think you must drop a view before changing it? Commit to your answer.

Concept: Learn how to change an existing view without dropping it first.

Many SQL systems support CREATE OR REPLACE VIEW view_name AS SELECT ...; This updates the view definition without removing it first, preserving permissions and dependencies.

Result

The view definition changes smoothly without losing access rights.

Understanding this syntax helps maintain views safely in production environments.

7

Expert

Performance and Security Implications of Views

🤔Before reading on: do you think views always improve query speed? Commit to your answer.

Concept: Explore how views affect performance and data security in real systems.

Views do not store data, so querying a view runs its underlying SELECT each time, which can be slow for complex queries. Materialized views store data to improve speed but need refreshing. Views can restrict user access by showing only certain columns or rows, enhancing security without duplicating data.

Result

Better control over data access but potential performance trade-offs.

Knowing when to use views or materialized views and how views affect security is key for professional database design.

Under the Hood

When you query a view, the database replaces the view name with its saved SELECT query and runs that combined query against the actual tables. The view itself does not hold data; it is a stored SQL statement. This means views always show current data from the tables. Permissions on views can restrict what users see without changing the underlying tables.

Why designed this way?

Views were designed to simplify complex queries and improve security by abstracting data access. Storing data in views would duplicate storage and cause synchronization problems. By storing only the query, views remain lightweight and always show fresh data. This design balances flexibility, performance, and security.

┌───────────────┐
│ User queries  │
│   the view   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ View definition│
│ (saved SELECT) │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Underlying     │
│ tables/data   │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does a view store data separately from tables? Commit yes or no.

Common Belief: A view stores its own copy of data like a table.

Reality: A view only stores the query definition, not the data itself. It shows live data from the underlying tables.

Why it matters: Thinking views store data can lead to confusion about data freshness and storage needs.

Quick: Can you always update data through any view? Commit yes or no.

Common Belief: You can update data through any view just like a table.

Reality: Only simple views based on one table without joins or aggregations allow updates. Complex views do not support updates.

Why it matters: Trying to update through unsupported views causes errors and wasted effort.

Quick: Does creating a view improve query speed automatically? Commit yes or no.

Common Belief: Views always make queries faster because they are pre-made.

Reality: Views run their underlying queries each time, so they can be slower. Materialized views are needed for speed improvements.

Why it matters: Assuming views speed up queries can cause performance problems in real systems.

Quick: Must you drop a view before changing its query? Commit yes or no.

Common Belief: You must drop a view before redefining it.

Reality: Many SQL systems support CREATE OR REPLACE VIEW to update views without dropping them.

Why it matters: Dropping views unnecessarily can break permissions and dependencies.

Expert Zone

1

Views can be nested, meaning a view can be defined using other views, which can complicate query optimization.

2

Some databases optimize views by merging their queries into the main query, but others do not, affecting performance.

3

Security policies can be layered using views to expose only specific data slices to different user roles without changing the base tables.

When NOT to use

Avoid using views for very large or complex queries that run frequently and need fast response; instead, use materialized views or indexed tables. Also, do not use views to hide business logic that should be in application code or stored procedures.

Production Patterns

In production, views are often used to simplify reporting queries, enforce data access rules, and provide backward compatibility when underlying tables change. They are combined with roles and permissions to secure sensitive data without duplicating it.

Connections

Materialized Views

Builds-on

Understanding views helps grasp materialized views, which store query results physically for faster access but require manual refresh.

Database Security

Builds-on

Views are a key tool in database security to restrict user access to specific columns or rows without changing the underlying tables.

Functional Programming

Same pattern

Views are like pure functions in programming: they produce results based on input (tables) without side effects or storing state.

Common Pitfalls

#1 Trying to update data through a complex view with joins.

Wrong approach:

UPDATE complex_view SET column = value WHERE id = 1;

Correct approach:

Update the underlying tables directly: UPDATE base_table SET column = value WHERE id = 1;

Root cause:

Misunderstanding that only simple views support updates; complex views are read-only.

#2 Assuming views improve query performance automatically.

Wrong approach:

CREATE VIEW slow_view AS SELECT * FROM huge_table JOIN another_table ON ...; -- expecting faster queries

Correct approach:

Use materialized views or optimize queries instead: CREATE MATERIALIZED VIEW fast_view AS SELECT ...;

Root cause:

Confusing views as stored data rather than saved queries executed on demand.

#3 Dropping a view before changing it, causing permission loss.

Wrong approach:

DROP VIEW my_view; CREATE VIEW my_view AS SELECT ...;

Correct approach:

CREATE OR REPLACE VIEW my_view AS SELECT ...;

Root cause:

Not knowing the CREATE OR REPLACE syntax preserves permissions and dependencies.

Key Takeaways

CREATE VIEW defines a named saved query that acts like a virtual table showing live data.

Views simplify complex queries and help secure data by restricting access to specific columns or rows.

Views do not store data themselves; they run their underlying SELECT queries each time they are used.

Only simple views support data updates; complex views are read-only.

Using CREATE OR REPLACE VIEW lets you modify views safely without dropping them first.