What is View

A View is one of the most powerful and commonly used database objects in SQL. It provides a way to create virtual tables that simplify data access, enhance security, and promote code reusability.

In this tutorial, you will learn:

• The definition and purpose of SQL views
• How views differ from regular tables
• Basic view syntax and structure
• When and why to use views

What is a SQL View?

A View is a virtual table based on the result of a SELECT statement. It doesn't store data itself but stores the query definition that produces data when accessed.

Key Characteristics

The Virtual Nature of Views

Understanding that views don't store data is crucial:

What Happens When You Query a View?

1. User runs: SELECT * FROM my_view
2. Database retrieves: The stored query definition
3. Database executes: The underlying SELECT statement
4. Database returns: Results as if from a table

The user sees results that look like they came from a table, but the data was actually fetched from one or more base tables at query time.

Creating a View

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

Syntax Components

Example Definitions

-- Simple view: Filter customers from São Paulo state
CREATE VIEW sp_customers AS
SELECT customer_id, customer_city, customer_state
FROM olist_customers_dataset
WHERE customer_state = 'SP';

-- View with JOIN: Customer orders
CREATE VIEW customer_orders AS
SELECT 
    c.customer_id,
    c.customer_city,
    o.order_id,
    o.order_status
FROM olist_customers_dataset c
JOIN olist_orders_dataset o ON c.customer_id = o.customer_id;

-- View with aggregation: State statistics
CREATE VIEW state_statistics AS
SELECT 
    customer_state,
    COUNT(*) AS customer_count,
    COUNT(DISTINCT customer_city) AS city_count
FROM olist_customers_dataset
GROUP BY customer_state;

Querying Views

Once created, views are used exactly like tables:

-- Simple SELECT from view
SELECT * FROM sp_customers;

-- SELECT with WHERE clause
SELECT * FROM sp_customers 
WHERE customer_city = 'sao paulo';

-- JOIN view with another table
SELECT v.*, p.payment_value
FROM customer_orders v
JOIN olist_order_payments_dataset p ON v.order_id = p.order_id;

-- Aggregate data from a view
SELECT customer_city, COUNT(*) AS order_count
FROM customer_orders
GROUP BY customer_city
ORDER BY order_count DESC;

Benefits of Using Views

1. Simplified Queries - Complex joins written once, used many times
2. Consistent Logic - Standard calculations defined in one place
3. Security - Expose only necessary columns/rows
4. Abstraction - Hide complex table structures from users

Categories of SQL Views

1. Simple Views

Based on a single table without complex operations:

-- Simple view: single table, basic filter
CREATE VIEW delivered_orders AS
SELECT order_id, customer_id, order_status
FROM olist_orders_dataset
WHERE order_status = 'delivered';

Characteristics:

• Single table source
• No aggregations
• No DISTINCT, GROUP BY, or HAVING
• Usually updatable (can INSERT/UPDATE through the view)

2. Complex Views

Involve joins, aggregations, or multiple tables:

-- Complex view: multiple tables with aggregation
CREATE VIEW order_summary AS
SELECT 
    c.customer_state,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(p.payment_value) AS total_revenue
FROM olist_customers_dataset c
JOIN olist_orders_dataset o ON c.customer_id = o.customer_id
JOIN olist_order_payments_dataset p ON o.order_id = p.order_id
GROUP BY c.customer_state;

Characteristics:

• Multiple table sources
• May include aggregations, JOINs, subqueries
• Usually read-only (cannot INSERT/UPDATE through view)

3. Materialized Views (Database-Specific)

Store computed results physically for performance:

-- Materialized view syntax (PostgreSQL, Oracle)
CREATE MATERIALIZED VIEW cached_stats AS
SELECT customer_state, COUNT(*) AS count
FROM customers
GROUP BY customer_state;

Note: SQLite does not support materialized views natively.

Let's explore practical examples of how views work with our e-commerce dataset. While we can't create views in our read-only environment, we can see the queries that would define them:

Key Takeaways

1. A View is a virtual table - It stores a query definition, not actual data

2. Syntax: CREATE VIEW name AS SELECT ...

3. Views are queried like tables - Use SELECT, WHERE, JOIN, etc.

4. Data is always current - Views fetch fresh data from base tables each time

5. Types of views:

• Simple views (single table)
• Complex views (joins, aggregations)
• Materialized views (cached results)

Quick Reference

-- Create a view
CREATE VIEW view_name AS
SELECT columns FROM table WHERE conditions;`

`-- Use a view
SELECT * FROM view_name WHERE filter;`

`-- Join with a view
SELECT * FROM view_name v
JOIN other_table t ON v.id = t.id;

What's Next?

Now that you understand what views are, let's compare Views vs Tables to understand the key differences and when to use each approach.