SQL Views

What Is a View?

A view is a named query stored in the database. When you select from a view, the database runs the query behind it and returns the result as if it were a regular table. The rows are not copied or stored separately; they are calculated fresh each time you query the view. This means a view always reflects the current data in its underlying tables.

Views are useful when you find yourself writing the same joins, filters, or calculations over and over. Instead of repeating that logic, you wrap it in a view once and then treat it like any other table in your future queries.

• Simplify complicated queries by hiding joins and conditions behind a friendly name.
• Present a consistent, reusable shape of the data to reports and applications.
• Restrict access by exposing only certain columns or rows to users.
• Reduce mistakes by keeping business logic in one place instead of scattered across many queries.

Creating a View

You define a view with the CREATE VIEW statement followed by a SELECT query. The columns returned by the SELECT become the columns of the view.

CREATE VIEW ActiveCustomers AS
SELECT CustomerID, CustomerName, City
FROM Customers
WHERE Active = 1;

Once the view exists, you query it exactly like a table. The database expands the definition and applies your extra conditions on top of it.

SELECT CustomerName, City
FROM ActiveCustomers
WHERE City = 'London'
ORDER BY CustomerName;

Updating and Dropping a View

To change what a view returns, use CREATE OR REPLACE VIEW with the new query. To remove a view entirely, use DROP VIEW. Dropping a view never affects the data in the underlying tables, because the view held no data of its own.

CREATE OR REPLACE VIEW ActiveCustomers AS
SELECT CustomerID, CustomerName, City, Country
FROM Customers
WHERE Active = 1;

DROP VIEW ActiveCustomers;