Querying through views in SQL - Deep Dive

Overview - Querying through views

What is it?

A view in SQL is like a saved query that acts as a virtual table. Querying through views means you write SQL commands to get data from these virtual tables instead of directly from the real tables. Views simplify complex queries by hiding details and can combine data from multiple tables. They do not store data themselves but show data from the underlying tables when asked.

Why it matters

Views make working with databases easier and safer. Without views, users would have to write complex queries every time, which can lead to mistakes and slow performance. Views also help protect sensitive data by showing only what is needed. Without views, managing and reusing complex queries would be much harder, making data access less efficient and more error-prone.

Where it fits

Before learning about querying through views, you should understand basic SQL queries and how tables work. After mastering views, you can learn about advanced topics like materialized views, query optimization, and database security. Views are a stepping stone between simple queries and more complex database management.

Mental Model

Core Idea

A view is a saved window into data that lets you query complex information easily as if it were a simple table.

Think of it like...

Imagine a view as a custom window in a house that shows only certain rooms or furniture arranged in a special way, so you don’t have to walk through the whole house to see what you want.

┌─────────────┐
│   View      │
│ (Virtual)   │
│  SELECT *   │
│ FROM TableA │
│ JOIN TableB │
└─────┬───────┘
      │
      ▼
┌─────────────┐
│  TableA     │
└─────────────┘
┌─────────────┐
│  TableB     │
└─────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding What a View Is

Concept: Introduce the idea of a view as a virtual table created by a saved query.

A view is not a real table but looks like one. It stores a SQL query that runs when you ask for data. For example, if you have a table of employees and a table of departments, a view can show employee names with their department names combined.

Result

You can query the view like a table, and it shows combined data without storing it separately.

Understanding that views are virtual tables helps you see how they simplify complex data without duplicating it.

2

Foundation

Creating and Querying a Simple View

Concept: Learn how to create a view and use SELECT to get data from it.

Use CREATE VIEW view_name AS followed by a SELECT query. For example: CREATE VIEW EmployeeDept AS SELECT e.name, d.department_name FROM Employees e JOIN Departments d ON e.dept_id = d.id; Then query it like: SELECT * FROM EmployeeDept;

Result

The query returns employee names with their department names as if from a single table.

Knowing how to create and query views lets you reuse complex joins easily.

3

Intermediate

Views Simplify Complex Queries

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

Concept: Views hide complexity by encapsulating joins, filters, and calculations inside them.

Instead of writing long queries every time, you create a view once. For example, a view can calculate total sales per customer by joining multiple tables and summing amounts. Then you just query the view to get the results.

Result

Queries become shorter, easier to read, and less error-prone.

Understanding that views encapsulate complexity helps you write cleaner and maintainable SQL.

4

Intermediate

Using Views for Data Security

🤔Before reading on: can views restrict which columns or rows users see? Commit to your answer.

Concept: Views can limit access to sensitive data by showing only certain columns or filtered rows.

For example, a view can show employee names but hide salaries. Or it can show only employees from a certain department. This way, users query the view and never see restricted data.

Result

Users get only the data they are allowed to see, improving security.

Knowing views can control data visibility is key for safe database design.

5

Advanced

Updating Data Through Views

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

Concept: Some views allow updates that change the underlying tables, but not all views support this.

If a view is simple (selecting from one table without joins or aggregates), you can run UPDATE, INSERT, or DELETE on it. But complex views with joins or calculations usually do not allow updates directly.

Result

You can sometimes modify data through views, but you must know the rules.

Understanding update rules prevents errors and helps design views that support data changes.

6

Expert

Performance Implications of Querying Views

🤔Before reading on: do you think querying a view is always as fast as querying a table? Commit to your answer.

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

When you query a view, the database runs the saved query behind it. If the view is complex, this can slow down queries. Some databases offer materialized views that store results to improve speed, but normal views do not.

Result

Querying views can be slower than tables if the view is complex or used often.

Knowing how views affect performance helps you decide when to use them or optimize queries.

Under the Hood

When you query a view, the database replaces the view name with the saved SELECT query behind it. This process is called query expansion. The database then executes the expanded query on the real tables. Views do not hold data themselves; they are just stored queries. This means every time you query a view, the underlying tables are accessed fresh, reflecting current data.

Why designed this way?

Views were designed as virtual tables to avoid data duplication and to simplify complex queries. Storing data separately would require synchronization and extra storage. By storing only the query, views remain lightweight and always show up-to-date data. This design balances ease of use with data integrity and storage efficiency.

┌───────────────┐
│ Query on View │
└───────┬───────┘
        │
        ▼
┌─────────────────────────────┐
│ View Definition (Stored SQL) │
└───────────────┬─────────────┘
                │
                ▼
┌─────────────────────────────┐
│ Expanded Query on Base Tables│
└───────────────┬─────────────┘
                │
                ▼
┌───────────────┐  ┌───────────────┐
│   Table A     │  │   Table B     │
└───────────────┘  └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does querying a view always return stored data instantly? Commit yes or no.

Common Belief: Views store data like tables, so querying them is always fast and returns stored results.

Reality: Views do not store data; they run the underlying query each time you ask for data.

Why it matters: Assuming views store data can lead to unexpected slow queries and confusion about data freshness.

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

Common Belief: You can update, insert, or delete data through any view just like a table.

Reality: Only simple views without joins or aggregates support updates; complex views do not.

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

Quick: Do views protect data by themselves without permissions? Commit yes or no.

Common Belief: Creating a view automatically secures data and prevents unauthorized access.

Reality: Views help limit data shown but require proper database permissions to be effective.

Why it matters: Relying on views alone for security can expose sensitive data if permissions are not set correctly.

Quick: Are views always better than writing queries directly? Commit yes or no.

Common Belief: Using views is always better because they simplify queries and improve performance.

Reality: Views simplify queries but can sometimes slow performance if overused or complex.

Why it matters: Blindly using views without considering performance can degrade application speed.

Expert Zone

1

Some databases optimize view queries by merging them with outer queries, but others do not, affecting performance.

2

Views can be nested, meaning a view can query another view, which can complicate debugging and optimization.

3

Materialized views store data physically and require manual or automatic refresh, unlike normal views that are always virtual.

When NOT to use

Avoid using views when performance is critical and the underlying query is very complex or large. Instead, consider materialized views or direct optimized queries. Also, do not use views to enforce security without proper permission controls.

Production Patterns

In production, views are often used to provide simplified APIs for reporting, to enforce data access rules, and to encapsulate business logic in the database. Materialized views are used for caching expensive query results. Views are also used to maintain backward compatibility when underlying tables change.

Connections

Materialized Views

Builds-on

Understanding normal views helps grasp materialized views, which store data physically to improve performance at the cost of freshness.

Database Permissions

Complementary

Knowing how views limit data visibility pairs with permissions to create secure data access layers.

Software APIs

Similar pattern

Views act like APIs in software, providing a controlled, simplified interface to complex underlying data.

Common Pitfalls

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

Wrong approach:

UPDATE EmployeeDept SET department_name = 'Sales' WHERE name = 'Alice';

Correct approach:

UPDATE Departments SET department_name = 'Sales' WHERE id = (SELECT dept_id FROM Employees WHERE name = 'Alice');

Root cause:

Misunderstanding that complex views are not updatable and updates must target base tables.

#2 Assuming views improve query performance automatically.

Wrong approach:

CREATE VIEW BigJoinView AS SELECT * FROM LargeTable1 JOIN LargeTable2 ON ...; SELECT * FROM BigJoinView WHERE condition;

Correct approach:

Optimize the underlying query or use a materialized view if performance is critical.

Root cause:

Believing views cache data or optimize queries without understanding their virtual nature.

#3 Using views alone to secure sensitive columns without setting permissions.

Wrong approach:

CREATE VIEW PublicView AS SELECT name FROM Employees; -- No permission changes

Correct approach:

CREATE VIEW PublicView AS SELECT name FROM Employees; GRANT SELECT ON PublicView TO public_user; REVOKE SELECT ON Employees FROM public_user;

Root cause:

Not combining views with proper permission management leads to data leaks.

Key Takeaways

Views are virtual tables defined by saved queries that simplify data access without storing data themselves.

Querying a view runs its underlying query each time, so views always show current data but may affect performance.

Views can hide complexity and restrict data visibility, but they require proper permissions to secure data effectively.

Only simple views support updating data through them; complex views do not allow direct data changes.

Understanding views helps build cleaner, safer, and more maintainable database queries and applications.