sql-using-views

SQL - Using Views

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Think of a view as a stored query that you can interact with as if it were a table. This provides a powerful mechanism for abstraction and security.

Benefits of Using Views

• Simplicity: A view can hide the complexity of a query involving multiple joins. Users can query the simple view instead of writing the complex join themselves.
• Security: Views can be used to expose only specific columns and rows to a user, restricting their access to sensitive data in the underlying base tables.
• Consistency: Views provide a consistent, stable interface to your data. Even if you refactor the underlying tables, you can maintain the view’s structure, preventing breaking changes in applications that use it.
• Readability: A well-named view (e.g., ActiveUsers, QuarterlySalesSummary) makes queries more intuitive and self-documenting.

Creating Views

Database views are created using the CREATE VIEW statement.

Syntax

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

Example

Let’s create a view called HighValueCustomers from our Customers table. This view will only show the name and city of customers whose salary is greater than 5000.

CREATE VIEW HighValueCustomers AS
SELECT Name, City, Salary
FROM Customers
WHERE Salary > 5000.00;

Now, you can query this view just like a regular table:

SELECT * FROM HighValueCustomers
ORDER BY Salary DESC;

This query would return a result containing only ‘Chaitali’, ‘Hardik’, and ‘Muffy’ from our original sample data, without the user needing to know the underlying filtering logic.

Updating Data Through a View

Some views are ‘updatable’, meaning you can use INSERT, UPDATE, and DELETE statements on them, and the changes will be passed through to the underlying base table. A view is generally updatable if:

• It is based on a single table.
• It does not use DISTINCT, aggregate functions (SUM, COUNT, etc.), GROUP BY, or HAVING.
• It does not use subqueries or set operators (UNION, etc.).
• It includes all NOT NULL columns from the base table that do not have a DEFAULT value (for INSERT to work).

The WITH CHECK OPTION

The WITH CHECK OPTION ensures that any INSERT or UPDATE performed through the view must conform to the view’s WHERE clause. This prevents a user from updating a row in such a way that it would no longer be visible through the view.

CREATE OR REPLACE VIEW HighValueCustomers AS
SELECT Name, City, Salary
FROM Customers
WHERE Salary > 5000.00
WITH CHECK OPTION;

With this option, an attempt to UPDATE HighValueCustomers SET Salary = 4000 WHERE Name = 'Chaitali' would fail, because the new salary of 4000 does not meet the WHERE Salary > 5000 condition.

Dropping Views

When a view is no longer needed, you can remove it using the DROP VIEW statement. This does not affect the data in the underlying base tables.

DROP VIEW HighValueCustomers;