Skip to main content

SQL Views

A View in SQL is a virtual table based on a result set of a SELECT query. Views can simplify complex queries, enhance security by limiting data access, and provide a more user-friendly way to access data.

Advantages of Views​

  • Simplicity: Abstract complex queries, allowing easier access to data.
  • Security: Restrict access to specific data by allowing users to query a view instead of underlying tables.
  • Data Consistency: Ensure data consistency by allowing standardized queries to run on views instead of individual tables.

Syntax​

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

Example​

Example 1: Creating a Simple View

Create a view named CustomerView that displays only selected columns from the Customers table.

CREATE VIEW CustomerView AS
SELECT CustomerID, CustomerName, Country
FROM Customers;

To query data from this view:

SELECT * FROM CustomerView;

Example 2: View with Join

Create a view that combines data from multiple tables to show order details.

CREATE VIEW OrderDetailsView AS
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate, Orders.Amount
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Example 3: Updating Data through a View

In some SQL databases, views can be updatable. This depends on database constraints and view complexity. Here’s an example of updating a view:

UPDATE CustomerView
SET Country = 'USA'
WHERE CustomerID = 1;

Note: Not all views are updatable. Complex views (e.g., those with joins, group functions) may not support updates.

Managing Views​

  • Modify a View: Use CREATE OR REPLACE VIEW to modify an existing view without dropping it.
CREATE OR REPLACE VIEW CustomerView AS
SELECT CustomerID, CustomerName
FROM Customers
WHERE Country = 'USA';
  • Delete a View: Use DROP VIEW to remove a view from the database.
DROP VIEW view_name;

Notes :

  • Virtual Table: A view doesn’t store data; it only stores the SQL query that defines it.
  • Read-Only Views: Some views are read-only depending on complexity and database constraints.
  • Permissions: Views can provide restricted data access to specific users without exposing full tables.

SQL Views are powerful tools that enhance database usability, security, and data management by abstracting and simplifying access to underlying tables.