Views

Create reusable virtual tables with views to simplify complex queries.

16 min read
Beginner

Introduction to Views

You've written complex queries with JOINs, subqueries, and aggregations. But imagine writing the same complex query over and over - tedious and error-prone! What if you could save that query and reuse it like a table?

That's exactly what views do! A view is a saved query that acts like a virtual table. You query it just like a regular table, but behind the scenes it runs your saved query.

A virtual table based on a saved SELECT query. It doesn't store data itself - it dynamically executes the underlying query whenever you access the view. Views simplify complex queries, improve security, and maintain consistent business logic.

Why use views?

  • Simplify complexity: Turn complex queries into simple SELECT statements
  • Reusability: Write once, use many times
  • Security: Hide sensitive columns from certain users
  • Consistency: Ensure everyone uses the same business logic
  • Abstraction: Hide database structure changes from applications

Think of a view as a "named query" or a "query shortcut."

Creating Your First View

Let's work with our familiar tables:

employees
id
name
dept_id
salary
hire_date
1Alice Smith10950002020-01-15
2Bob Johnson20750002021-03-20
3Carol White101050002019-06-10
4David Brown30650002022-01-05
departments
id
dept_name
location
10EngineeringNew York
20MarketingLondon
30SalesChicago

Syntax:

CREATE VIEW view_name AS
SELECT ...
FROM ...
WHERE ...;

Let's create a simple view:

sql
-- Create a view of employee names and departments
CREATE VIEW employee_departments AS
SELECT
    e.id,
    e.name,
    d.dept_name,
    d.location
FROM employees e
INNER JOIN departments d
  ON e.dept_id = d.id;

Now use it like a regular table:

sql
-- Query the view
SELECT * FROM employee_departments;

What happened:

  1. We saved a complex JOIN query as a view
  2. Now we can SELECT * FROM employee_departments instead of writing the JOIN every time
  3. The view doesn't store data - it runs the JOIN query when accessed

Benefits of Views - Simplifying Complexity

Without views, you'd write this every time:

sql
-- Without views: complex query every time
SELECT
    e.name,
    d.dept_name,
    d.location
FROM employees e
INNER JOIN departments d
  ON e.dept_id = d.id
WHERE d.location = 'New York';

With views, it's simple:

sql
-- With views: clean and simple
SELECT name, dept_name, location
FROM employee_departments
WHERE location = 'New York';

Views with Calculations and Aggregations

Views can contain any SELECT query - including calculations:

sql
-- View with salary calculations
CREATE VIEW employee_summary AS
SELECT
    e.id,
    e.name,
    e.salary,
    d.dept_name,
    TIMESTAMPDIFF(YEAR, e.hire_date, CURRENT_DATE) AS years_employed,
    CASE
        WHEN e.salary >= 100000 THEN 'High'
        WHEN e.salary >= 75000 THEN 'Medium'
        ELSE 'Entry'
    END AS salary_tier
FROM employees e
INNER JOIN departments d
  ON e.dept_id = d.id;
sql
SELECT * FROM employee_summary;

Now all users get consistent salary tier calculations!

Views with Aggregations

Create summary views with GROUP BY:

sql
-- Department summary view
CREATE VIEW dept_stats AS
SELECT
    d.dept_name,
    d.location,
    COUNT(e.id) AS employee_count,
    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.dept_id
GROUP BY d.id, d.dept_name, d.location;
sql
SELECT * FROM dept_stats
ORDER BY employee_count DESC;

This summary is now available to everyone without rewriting the aggregation!

Querying Views with Additional Filters

Views can be filtered, sorted, and combined just like tables:

sql
-- Filter view results
SELECT *
FROM employee_summary
WHERE salary_tier = 'High'
  AND years_employed > 4;
sql
-- Sort view results
SELECT name, dept_name, salary
FROM employee_summary
ORDER BY salary DESC
LIMIT 2;

Views for Security - Hiding Sensitive Data

Views can hide sensitive columns from certain users:

sql
-- Public employee directory (no salary info)
CREATE VIEW public_employee_directory AS
SELECT
    e.name,
    d.dept_name,
    d.location,
    e.hire_date
FROM employees e
INNER JOIN departments d
  ON e.dept_id = d.id;
sql
SELECT * FROM public_employee_directory;

Security benefit: Grant regular users access to public_employee_directory instead of the employees table. They can see names and departments but not salaries!

Viewing Existing Views

See all views in your database:

sql
-- MySQL/MariaDB
SHOW FULL TABLES WHERE Table_type = 'VIEW';

PostgreSQL:

\dv

Show view definition:

SHOW CREATE VIEW employee_departments;

Dropping Views

Remove a view when no longer needed:

sql
DROP VIEW IF EXISTS employee_departments;

Important: Dropping a view only removes the saved query definition. It does NOT delete any data from the underlying tables. The original tables remain unchanged.

Updating Views - CREATE OR REPLACE

Modify an existing view:

sql
-- Update view definition
CREATE OR REPLACE VIEW employee_departments AS
SELECT
    e.id,
    e.name,
    d.dept_name,
    d.location,
    e.salary  -- Added salary column
FROM employees e
INNER JOIN departments d
  ON e.dept_id = d.id;

Alternative approach:

DROP VIEW IF EXISTS employee_departments;
CREATE VIEW employee_departments AS ...

Practical Example: Sales Dashboard View

Create a comprehensive reporting view:

orders
order_id
customer_id
order_date
total
status
112024-01-15500.00completed
222024-02-20350.00completed
312024-03-10750.00pending
customers
customer_id
name
city
country
1Alice CorpNew YorkUSA
2Bob IncLondonUK
sql
-- Sales dashboard view
CREATE VIEW sales_dashboard AS
SELECT
    o.order_id,
    c.name AS customer,
    c.city,
    c.country,
    o.order_date,
    o.total,
    o.status,
    YEAR(o.order_date) AS year,
    QUARTER(o.order_date) AS quarter,
    MONTHNAME(o.order_date) AS month,
    CASE
        WHEN o.total >= 500 THEN 'Large'
        WHEN o.total >= 200 THEN 'Medium'
        ELSE 'Small'
    END AS order_size,
    DATEDIFF(CURRENT_DATE, o.order_date) AS days_ago
FROM orders o
INNER JOIN customers c
  ON o.customer_id = c.customer_id;
sql
-- Use the dashboard view for reports
SELECT
    customer,
    COUNT(*) AS order_count,
    SUM(total) AS total_revenue,
    AVG(total) AS avg_order_value
FROM sales_dashboard
WHERE status = 'completed'
GROUP BY customer
ORDER BY total_revenue DESC;

Views vs Tables - Key Differences

Views vs Tables
Aspect
View
Table
Stores data?No (virtual)Yes (physical)
Query executionRuns underlying query each timeDirect data access
Disk spaceMinimal (just query definition)Stores all data
Data freshnessAlways currentCan become outdated
PerformanceDepends on underlying queryGenerally faster
Can update data?Sometimes (simple views only)Yes

Common Mistakes to Avoid

Don't make these mistakes:

  1. Selecting * in views: ❌

    • CREATE VIEW my_view AS SELECT * FROM table
    • If table structure changes, view might break
    • Better: explicitly list columns

  2. Complex views with poor performance: ⚠️

    • Views with multiple JOINs and subqueries can be slow
    • Consider materialized views or indexed views for performance

  3. Circular view references: ❌

    • View A references View B, View B references View A
    • Creates infinite loop - will fail

  4. Forgetting view dependencies: ❌

    • Dropping a table breaks views that use it
    • Check dependencies before dropping tables

  5. Assuming views improve performance: ❌

    • Views don't cache results (regular views)
    • They just save typing, not execution time

  6. Updating data through views: ⚠️

    • Only simple views (single table, no aggregations) allow INSERT/UPDATE
    • Most complex views are read-only

  7. Not using OR REPLACE: ❌

    • Without OR REPLACE, you must DROP VIEW first
    • Can lose permissions if you drop and recreate

Key Takeaways

What you learned:

✅ View = saved SELECT query that acts like a virtual table ✅ CREATE VIEW name AS SELECT ... - creates a view ✅ Views don't store data - they run the query each time ✅ Benefits: simplify complexity, reusability, security, consistency ✅ Query views like tables: SELECT * FROM view_name ✅ Views can have JOINs, calculations, aggregations, filters ✅ CREATE OR REPLACE VIEW - update view definition ✅ DROP VIEW - delete view (doesn't affect underlying tables) ✅ Views hide sensitive columns for security ✅ Use views for complex queries you run frequently

Real-world applications:

  • Reporting dashboards
  • User permission management
  • Simplifying complex business logic
  • Data abstraction layers
  • Consistent calculated fields

Practice Exercise: Create these views:

  1. high_earners - employees with salary > 90000, include name and department
  2. recent_hires - employees hired in the last year
  3. dept_summary - department name, employee count, average salary
  4. employee_full_info - combine employees with departments, calculate tenure in years
  5. top_spenders - customers with their total order value > 1000
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.