Views

As SQL queries grow more complex, repeating the same logic again and again becomes inefficient and error-prone.

Views solve this problem by allowing you to save a query as a virtual table that can be reused easily.

What is a View?

A View is a stored SQL query that behaves like a table, but does not store data itself.

Instead, a view:

• Stores the SQL logic
• Runs the query each time the view is accessed
• Always shows up-to-date data

Why Use Views?

Views are commonly used to:

• Simplify complex queries
• Improve readability and maintainability
• Provide controlled access to data
• Reuse logic across reports and dashboards

Basic View Syntax

Creating a view:

CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE condition;
  

Once created, the view can be queried like a table.

Example Table

We will continue using the employees table:

+----+----------+------------+--------+
| id | name     | department | salary |
+----+----------+------------+--------+
| 1  | Alice    | IT         | 75000  |
| 2  | Bob      | IT         | 72000  |
| 3  | Charlie  | HR         | 60000  |
| 4  | Diana    | Finance    | 70000  |
| 5  | Evan     | HR         | 58000  |
+----+----------+------------+--------+
  

Creating a Simple View

Create a view that shows only IT department employees.

CREATE VIEW it_employees AS
SELECT name, salary
FROM employees
WHERE department = 'IT';
  

Now query the view:

SELECT * FROM it_employees;
  

The view behaves just like a table.

Views with Joins

Views are especially useful for hiding complex JOIN logic.

Example: Create a view combining employees and departments.

CREATE VIEW employee_details AS
SELECT e.name, d.department_name, e.salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
  

Now users can query:

SELECT * FROM employee_details;
  

Updating Data Through Views

Some views allow INSERT, UPDATE, or DELETE operations, but this depends on:

• The database system
• View complexity
• Presence of joins or aggregates

As a best practice, treat views as read-only unless explicitly required.

Replacing or Dropping a View

Replace an existing view:

CREATE OR REPLACE VIEW it_employees AS
SELECT name, salary
FROM employees
WHERE department = 'IT' AND salary > 70000;
  

Drop a view:

DROP VIEW it_employees;
  

Views vs Tables

Common Beginner Mistakes

• Assuming views store data
• Overusing views for simple queries
• Updating data through complex views
• Forgetting to manage view dependencies

When Should You Use Views?

Use views when:

• You reuse the same query frequently
• You want cleaner, simpler SQL for users
• You need controlled access to sensitive columns

What’s Next?

In the next lesson, we will explore UNION and UNION ALL, which allow us to combine results from multiple queries.