What are views in SQL?

Views in SQL are a simple and powerful feature that makes working with data easier. They allow us to create virtual tables from queries without storing the data.

Suppose you are managing a library. You want a quick way to see all books borrowed this month without searching through every record repeatedly. SQL views can help—acting as personalized windows to your data. They let you save a search query and access its results dynamically without affecting the underlying data.

What is a view in SQL?

A view in SQL is a virtual table that is based on a SELECT query. It does not store any data on its own but retrieves it from one or more existing tables. Views are useful for simplifying complex queries and controlling access to specific data.

For example, if you frequently need to see only the employees from the HR department, instead of writing the same query every time, you can create a view for it.

Syntax of a view

The syntax for creating a view is simple:

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

  • view_name: The name you give to the view.

  • SELECT: The query that defines what the view will display.

  • WHERE: An optional clause to filter data.

Let’s see how this works in practice.

Create views in SQL

Creating a view helps you save time by reusing the same query. You can also hide specific columns from users for security purposes.

Example 1: Create view

-- Step 1: Create the database Company
CREATE DATABASE Company;
use Company;
-- Step 2: Create the Employees table  
CREATE TABLE Employees (  
    EmployeeID INT PRIMARY KEY,  
    Name VARCHAR(50),  
    Department VARCHAR(50),  
    Salary DECIMAL(10, 2)  
);  
-- Step 3: Insert sample data into the Employees table  
INSERT INTO Employees (EmployeeID, Name, Department, Salary)  
VALUES  
    (1, 'Alice', 'HR', 60000),  
    (2, 'Bob', 'Finance', 75000),  
    (3, 'Charlie', 'HR', 58000),  
    (4, 'Diana', 'IT', 72000);  
-- Step 4: Create a view to display only HR employees  
CREATE VIEW EmployeeView AS  
SELECT EmployeeID, Name, Department  
FROM Employees  
WHERE Department = 'HR';  
-- Step 5: Query the view to display the data  
SELECT * FROM EmployeeView;

This creates a view EmployeeView that shows only the employees working in the HR department.

Try experimenting by modifying the WHERE clause to view employees from another department (e.g., WHERE Department = 'IT').

Example 2: Calculations in a view

-- The SalaryView includes a bonus calculation which is 10% of the base salary for each employee.
CREATE VIEW SalaryView AS
SELECT EmployeeID,                 -- Displaying the unique identifier for each employee
       Name,                        -- Displaying the employee's name
       BaseSalary,                  -- Displaying the employee's base salary
       BaseSalary * 0.1 AS Bonus    -- Calculating the bonus as 10% of the BaseSalary and displaying it as 'Bonus'
FROM Employees;                    -- Fetching data from the 'Employees' table

This adds a calculated column Bonus to the SalaryView, showing 10% of the base salary for each employee.

View update in SQL

Sometimes, you might need to change the definition of a view. Instead of deleting and recreating it, you can use the CREATE OR REPLACE VIEW statement.

Example: Update view

-- The EmployeeView now includes an additional column for the JoinDate and filters employees from the 'HR' department.
CREATE OR REPLACE VIEW EmployeeView AS
SELECT EmployeeID,                 -- Displaying the unique identifier for each employee
       Name,                        -- Displaying the employee's name
       Department,                  -- Displaying the department where the employee works
       JoinDate                     -- Displaying the employee's join date
FROM Employees                      -- Fetching data from the 'Employees' table
WHERE Department = 'HR';            -- Filtering to show only employees from the HR department

This updates EmployeeView to include a new column, JoinDate.

Drop views in SQL

If you no longer need a view, you can delete it using the DROP VIEW statement.

Syntax

DROP VIEW view_name;

Example: Drop view

-- Step 3: Drop the 'EmployeeView' from the database
-- This command deletes the existing view named 'EmployeeView'.
-- Be cautious when using DROP, as it permanently removes the view, and you will need to recreate it if necessary.
DROP VIEW EmployeeView;
Drop view in SQL

This deletes the EmployeeView from the database.

Pros and cons of views

Pros of views

Cons of views

Makes frequently used or complex queries easier to manage.

Complex views can slow down query performance.

Exposes only specific columns or rows to users.

Most views are read-only and cannot directly modify data.

Allows multiple users or applications to use the same logic without rewriting it.

Changing the base table structure may require view adjustments.

Uses of a view

Views are highly useful in many situations:

  1. Simplify data access: Combine data from multiple tables for easier use.

  2. Secure data: Limit user access to sensitive or unnecessary columns.

  3. Data aggregation: Summarize data for reports, such as totals and averages.

  4. Reusable logic: Define complex queries once and reuse them across applications.

Key takeaways

  • A view is a virtual table created from a SQL query.

  • Use CREATE VIEW to define views and DROP VIEW to remove them.

  • Views are great for simplifying data access and improving security, but they can have performance trade-offs.

  • Understanding views is an important step in learning SQL and managing databases efficiently.

Ready to learn SQL and become a database expert? Join our Learn SQL course and follow the path to becoming a Database Professional with SQL! Unlock your potential and gain the skills employers seek. Start your journey today!

Frequently asked questions

Haven’t found what you were looking for? Contact Us

Why use a view instead of a table?

  • Simplification: Views simplify complex queries by storing the logic in the view definition. This makes it easier to reuse the same logic without having to write the query repeatedly.
  • Security: Views allow you to restrict access to specific columns or rows of a table. You can provide users access to only the data they need without exposing sensitive information.
  • Data abstraction: Views help abstract underlying data complexities, providing a more user-friendly or business-specific perspective of the data.

What is the difference between a view and a query?

  • View: A view is a virtual table that stores a predefined query. It does not store data itself but retrieves it dynamically from underlying tables when accessed. Once created, a view can be reused, making it convenient for repetitive data retrieval.
  • Query: A query is a one-time request for data from one or more tables. It can be executed immediately but doesn’t persist after execution unless saved as a view or stored procedure. Views encapsulate queries for easier reuse.

Is a view faster than a table?

  • A view does not store any data, so it must always execute the underlying query each time it is accessed, which may impact performance, especially for complex views.
  • Tables, on the other hand, store data physically, so retrieving data from a table is generally faster than executing a view query each time. The performance of a view depends on the complexity of the underlying query.

What are the disadvantages of views in SQL?

  • Performance overhead: Complex views, especially those involving multiple joins or calculations, can slow down performance, as the view query needs to be executed every time the view is accessed.
  • Read-Only limitation: Most views are read-only, meaning you cannot directly insert, update, or delete data through the view unless specific conditions are met. This can limit their flexibility.
  • Dependency issues: If the structure of the underlying tables changes (such as adding or removing columns), the views depending on those tables may break or need to be updated.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)