View as a saved query mental model in SQL

Introduction

A view lets you save a query so you can use it again easily without rewriting it.

You want to simplify complex queries for yourself or others.

You need to reuse the same query in many places.

You want to hide complicated details from users.

You want to give users a simple way to see specific data.

You want to keep your database organized and clean.

Syntax

SQL

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

A view acts like a virtual table based on the query you write.

You can select from a view just like a regular table.

Examples

This view shows only customers who are active.

SQL

CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';

This view lists product IDs with their prices.

SQL

CREATE VIEW product_prices AS
SELECT product_id, price
FROM products;

This view shows orders placed after January 1, 2024.

SQL

CREATE VIEW recent_orders AS
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date > '2024-01-01';

Sample Program

We create a table of employees, add some data, then create a view called sales_team that shows only employees in Sales. Finally, we select all from the view.

SQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'Sales', 50000),
(2, 'Bob', 'HR', 45000),
(3, 'Charlie', 'Sales', 55000);

CREATE VIEW sales_team AS
SELECT id, name, salary
FROM employees
WHERE department = 'Sales';

SELECT * FROM sales_team;

Important Notes

Views do not store data themselves; they run the saved query when you use them.

If the data in the original tables changes, the view shows the updated data.

You can use views to improve security by showing only certain columns or rows.

Summary

A view saves a query so you can use it like a table.

Views help simplify and reuse queries easily.

Views always show current data from the original tables.