Why views are needed in SQL - Why It Works This Way

Overview - Why views are needed

What is it?

A view in a database is like a saved query that looks like a table. It shows data from one or more tables but does not store the data itself. Views let you see data in a way that is easier to understand or more useful for specific tasks.

Why it matters

Views help simplify complex data by hiding complicated queries behind a simple name. Without views, users would have to write long, complex queries every time they want to see data in a certain way. This can lead to mistakes and slow work. Views also help protect sensitive data by showing only what users need to see.

Where it fits

Before learning about views, you should understand basic SQL queries and how tables work. After views, you can learn about advanced topics like stored procedures, triggers, and database security.

Mental Model

Core Idea

A view is a virtual table that shows data from one or more tables using a saved query to simplify and secure data access.

Think of it like...

A view is like a restaurant menu that shows you selected dishes without revealing the full kitchen or recipe details. You see only what you need in a simple way.

┌─────────────┐       ┌─────────────┐
│   Tables   │──────▶│    View     │
│ (Data)    │       │ (Saved SQL) │
└─────────────┘       └─────────────┘
         ▲                    │
         │                    ▼
   Raw data stored      Simplified data shown
   in tables only       when you query the view

Build-Up - 6 Steps

1

Foundation

Understanding Tables and Queries

Concept: Learn what tables and queries are in a database.

Tables store data in rows and columns, like a spreadsheet. Queries ask questions to get specific data from tables. For example, SELECT * FROM employees gets all employee data.

Result

You can retrieve data from tables using simple queries.

Knowing how tables and queries work is essential because views are built on queries that pull data from tables.

2

Foundation

What is a View in SQL?

Concept: Introduce the idea of a view as a saved query that acts like a virtual table.

A view is created by writing a query and saving it with a name. When you ask for data from the view, the database runs the saved query and shows the result as if it were a table.

Result

You can use views to simplify complex queries and reuse them easily.

Understanding that views do not store data but show results of queries helps avoid confusion about storage and performance.

3

Intermediate

Simplifying Complex Queries with Views

🤔Before reading on: do you think views store data or just show data? Commit to your answer.

Concept: Views help hide complex joins and filters behind a simple name.

Imagine you often join several tables to get customer orders with details. Writing this join every time is hard. Instead, create a view with that join. Now you just SELECT * FROM that view.

Result

Queries become shorter and easier to read when using views.

Knowing views simplify repeated complex queries saves time and reduces errors in writing SQL.

4

Intermediate

Using Views for Data Security

🤔Before reading on: do you think views can hide some columns or rows from users? Commit to your answer.

Concept: Views can restrict what data users see by showing only certain columns or filtered rows.

If a table has sensitive data like salaries, create a view that excludes that column. Give users access only to the view, not the full table.

Result

Users see only allowed data, protecting sensitive information.

Understanding views as a security layer helps control data access without changing the original tables.

5

Advanced

Performance Considerations of Views

🤔Before reading on: do you think views always make queries faster? Commit to your answer.

Concept: Views do not store data, so querying a view runs the underlying query each time, which can affect performance.

Because views are virtual, complex views can slow down queries if the underlying tables are large or joins are complicated. Some databases support materialized views that store data for faster access.

Result

Using views wisely balances simplicity and performance.

Knowing views can impact performance helps you decide when to use them or choose alternatives like materialized views.

6

Expert

Advanced Uses and Limitations of Views

🤔Before reading on: can views be updated like tables? Commit to your answer.

Concept: Views can sometimes be updated, but there are rules and limitations depending on the database and view complexity.

Simple views on one table can allow updates, inserts, or deletes that affect the base table. Complex views with joins or aggregations usually cannot be updated directly. Understanding these rules helps avoid errors.

Result

You can use views for read-only or sometimes read-write access depending on design.

Knowing the update rules for views prevents unexpected errors and guides proper database design.

Under the Hood

When you query a view, the database replaces the view name with the saved query behind it and runs that query on the base tables. The result is then returned as if it came from a table. Views do not store data themselves, so they always reflect the current data in the base tables.

Why designed this way?

Views were designed to provide a flexible way to simplify complex queries and control data access without duplicating data. Storing data in multiple places leads to inconsistencies and extra storage costs. Views avoid these problems by being virtual.

┌───────────────┐
│   User Query  │
│ SELECT * FROM │
│     View      │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ View Definition (Saved Query)│
│ SELECT columns FROM tables   │
│ WHERE conditions             │
└─────────────┬───────────────┘
              │
              ▼
┌─────────────────────────────┐
│ Base Tables with Actual Data │
└─────────────────────────────┘
              │
              ▼
┌─────────────────────────────┐
│ Query Result Returned to User│
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do views store data permanently like tables? Commit to yes or no.

Common Belief: Views store data just like tables do.

Reality: Views do not store data; they run the saved query on base tables each time you use them.

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

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

Common Belief: You can update data through any view as if it were a table.

Reality: Only simple views on one table without aggregations or joins can be updated directly; complex views usually cannot.

Why it matters: Trying to update complex views causes errors and wasted time.

Quick: Do views always improve query speed? Commit to yes or no.

Common Belief: Using views always makes queries faster.

Reality: Views can slow down queries because they run the underlying query each time; performance depends on complexity and data size.

Why it matters: Assuming views speed up queries can cause unexpected slowdowns in applications.

Quick: Does restricting access to a view fully secure the underlying tables? Commit to yes or no.

Common Belief: Giving access to a view means users cannot access the underlying tables at all.

Reality: If users have other ways to access tables, views alone do not guarantee security; proper permissions are needed.

Why it matters: Relying only on views for security can expose sensitive data unintentionally.

Expert Zone

1

Views can be nested, meaning a view can be built on top of another view, which can complicate performance and debugging.

2

Some databases support indexed or materialized views that store data physically to improve performance, but they require maintenance to stay updated.

3

The optimizer in the database may rewrite queries involving views to improve performance, but this behavior varies and can affect query plans.

When NOT to use

Avoid using views when performance is critical and the view query is very complex or runs on huge data sets. Instead, consider materialized views, denormalization, or caching results in application code.

Production Patterns

In real systems, views are used to provide simplified reports, enforce data access rules, and create reusable query components. They often serve as an abstraction layer between applications and raw tables.

Connections

Stored Procedures

Both encapsulate SQL logic but stored procedures can perform actions and control flow, while views only show data.

Understanding views helps grasp how databases separate data presentation (views) from data manipulation (procedures).

Object-Oriented Programming (OOP) Encapsulation

Views encapsulate complex queries behind a simple interface, similar to how OOP hides complex code inside objects.

Knowing this connection helps appreciate views as a way to manage complexity and improve code reuse.

Access Control in Security

Views act like filters controlling what data users can see, similar to how security policies restrict access to resources.

Recognizing views as part of access control helps design safer database systems.

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 base tables directly or create an INSTEAD OF trigger to handle updates on the view.

Root cause:

Misunderstanding that complex views are not directly updatable leads to errors.

#2 Assuming views improve query performance automatically.

Wrong approach:

Using a view with multiple joins on large tables expecting faster results without testing.

Correct approach:

Analyze query plans and consider materialized views or indexes for performance-critical queries.

Root cause:

Confusing simplicity of use with speed causes inefficient database design.

#3 Granting users access to views but forgetting to restrict base tables.

Wrong approach:

GRANT SELECT ON view TO user; but user also has SELECT on base tables.

Correct approach:

Revoke or restrict permissions on base tables to enforce security through views.

Root cause:

Overlooking comprehensive permission management weakens data security.

Key Takeaways

Views are virtual tables created by saving SQL queries to simplify data access and improve security.

They do not store data but show current data from base tables every time they are queried.

Views help hide complexity and protect sensitive data by showing only selected columns or rows.

Not all views can be updated, and using views does not always improve performance.

Understanding views is essential for building clean, secure, and maintainable database applications.