Views
## Learning Objectives
- Create and use views
- Understand view benefits
- Update through views
- Drop views safely
## What is a View?
A virtual table based on a query:
```sql
CREATE VIEW active_employees AS
SELECT
first_name,
last_name,
department,
salary
FROM employees
WHERE is_active = TRUE;
```
### How Views Work
```text
employees table
+-----------+-------------+
| id | name | is_active |
+-----------+-------------+
| 1 | Alice| true |
| 2 | Bob | false |
| 3 | Carol| true |
+-----------+-------------+
active_employees view
+-----------+-------------+
| name | department |
+-------+---------------+
| Alice| Engineering |
| Carol| Sales |
+-------+---------------+
```
## Creating Views
### Basic View
```sql
CREATE VIEW sales_employees AS
SELECT
employee_id,
first_name,
last_name,
salary
FROM employees
WHERE department = 'Sales';
```
### Complex View
```sql
CREATE VIEW department_summary AS
SELECT
d.department_name,
COUNT(e.employee_id) AS headcount,
AVG(e.salary) AS avg_salary,
MIN(e.salary) AS min_salary,
MAX(e.salary) AS max_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.department_name;
```
## Using Views
### Query a View
```sql
SELECT *
FROM active_employees
ORDER BY last_name;
```
### View in JOIN
```sql
SELECT
v.employee_name,
d.department_name
FROM sales_employees v
JOIN departments d ON v.department_id = d.id;
```
## Types of Views
### Updatable Views
Can INSERT, UPDATE, DELETE:
```sql
-- Simple view from single table
CREATE VIEW simple_view AS
SELECT first_name, last_name, email
FROM employees;
```
### Non-Updatable Views
Cannot modify:
- Views with aggregate functions
- Views with DISTINCT
- Views with GROUP BY
- Views with UNION
- Views from multiple tables
## INSERT through View
### Simple View
```sql
INSERT INTO simple_view (first_name, last_name, email)
VALUES ('Dave', 'Brown', 'dave@company.com');
```
### With CHECK OPTION
```sql
CREATE VIEW active_employees AS
SELECT *
FROM employees
WHERE is_active = TRUE
WITH CHECK OPTION;
-- This INSERT fails (is_active = FALSE would violate WHERE)
INSERT INTO active_employees
VALUES (4, 'Dave', 'Brown', 'dave@company.com', 'Sales', FALSE);
```
## DROP VIEW
### Basic Drop
```sql
DROP VIEW sales_employees;
```
### IF EXISTS
```sql
DROP VIEW IF EXISTS sales_employees;
```
### CASCADE
```sql
DROP VIEW sales_employees CASCADE;
```
## Benefits of Views
### Simplify Complex Queries
```sql
-- Instead of writing complex query each time:
CREATE VIEW monthly_sales AS
SELECT
DATE_TRUNC('month', sale_date) AS month,
SUM(amount) AS total_sales,
COUNT(*) AS num_sales
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);
-- Easy to query:
SELECT * FROM monthly_sales ORDER BY month;
```
### Security
```sql
-- Only allow access to certain columns
CREATE VIEW employee_public AS
SELECT first_name, last_name, department
FROM employees;
```
### Data Abstraction
```sql
-- Change underlying table without changing application
CREATE VIEW employee_list AS
SELECT employee_id, first_name, last_name
FROM employees_v2; -- Points to new table
```
## Replacing Views
### OR REPLACE
```sql
CREATE OR REPLACE VIEW active_employees AS
SELECT
employee_id,
first_name,
last_name,
salary,
hire_date
FROM employees
WHERE is_active = TRUE;
```
### Limitations
Must preserve the same column set and names.
## System Views
### MySQL
```sql
-- See all views
SELECT * FROM information_schema.views;
-- See view definitions
SHOW CREATE VIEW view_name;
```
### PostgreSQL
```sql
-- See all views
SELECT * FROM information_schema.views;
-- See definition
SELECT view_definition FROM information_schema.views
WHERE table_name = 'view_name';
```
## Materialized Views
### PostgreSQL (Not Standard SQL)
```sql
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
DATE_TRUNC('month', sale_date) AS month,
SUM(amount) AS total_sales
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);
-- Refresh when needed
REFRESH MATERIALIZED VIEW monthly_sales;
```
### Difference
| View | Materialized View |
|------|-------------------|
| Virtual (query runs each time) | Physical (stored results) |
| Always up-to-date | Needs refresh |
| No storage cost | Stores data on disk |
## Summary
- Views are virtual tables based on queries
- Simplify complex queries
- Provide security layer
- Can be updatable (simple views) or read-only
- Use **WITH CHECK OPTION** to enforce WHERE clauses
- **DROP VIEW** removes views
- **CREATE OR REPLACE** updates view definitions
- Materialized views store actual data (PostgreSQL)
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →