Views

🪟 SQL Views: Your Magic Windows Into Data

The Story of the Magic Window

Imagine you live in a huge library with millions of books. Every time your friend asks “What mystery books do you have?”, you’d have to run around collecting them. Exhausting, right?

Now imagine you had a magic window. You look through it, and poof — you see ONLY mystery books, perfectly arranged. The books never move. The window just shows them to you in a special way!

That’s exactly what a VIEW is in SQL!

A VIEW is a saved question (query) that acts like a virtual table. The data stays where it is — the VIEW just gives you a special way to look at it.

🎯 What We’ll Learn

graph TD
    A["Views Fundamentals"] --> B["CREATE VIEW"]
    B --> C["Updatable Views"]
    C --> D["View Restrictions"]

1️⃣ Views Fundamentals

What IS a View?

Think of your phone’s photo album called “Favorites”.

• The photos don’t move to a new folder
• The album is just a special way to see certain photos
• When you add a new favorite, it shows up automatically!

A VIEW works the same way:

• It doesn’t store data itself
• It’s a saved SELECT query with a name
• When you query the view, it runs the saved query fresh

Why Use Views?

Real Example

You have an employees table with salary info. Not everyone should see salaries!

Without a View:

-- HR runs this every time
SELECT name, department,
       hire_date
FROM employees;

With a View:

-- Create once, use forever!
-- (We'll learn how next)

2️⃣ CREATE VIEW

The Magic Words

Creating a view is like naming your magic window:

CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE conditions;

Your First View!

Let’s create a view for our library example:

CREATE VIEW mystery_books AS
SELECT title, author, year
FROM books
WHERE genre = 'Mystery';

Now you can use it like a table:

SELECT * FROM mystery_books;

🎉 So simple! The database remembers your saved query.

Example: Employee Directory

CREATE VIEW employee_directory AS
SELECT
    emp_id,
    first_name,
    last_name,
    department,
    email
FROM employees
WHERE status = 'Active';

Notice: No salary column! Safe to share with everyone.

Using Your View

-- Get all active employees
SELECT * FROM employee_directory;

-- Filter further
SELECT * FROM employee_directory
WHERE department = 'Sales';

-- Join with other tables
SELECT d.*, o.office_name
FROM employee_directory d
JOIN offices o ON d.department = o.dept;

View with Calculations

Views can include formulas too!

CREATE VIEW order_summary AS
SELECT
    order_id,
    customer_name,
    quantity * price AS total,
    order_date
FROM orders;

Now total is calculated automatically every time!

3️⃣ Updatable Views

Can You Change Data Through a Window?

Here’s a cool question: If you see books through your magic window, can you move a book?

Sometimes YES! These are called Updatable Views.

Simple Rule

If the VIEW shows data from ONE table in a simple way, you can usually UPDATE, INSERT, or DELETE through it.

Example: Updatable View

CREATE VIEW active_products AS
SELECT product_id, name, price
FROM products
WHERE is_active = 1;

You CAN do this:

-- Update through the view
UPDATE active_products
SET price = 29.99
WHERE product_id = 101;

-- Insert through the view
INSERT INTO active_products
    (product_id, name, price)
VALUES (201, 'New Item', 19.99);

-- Delete through the view
DELETE FROM active_products
WHERE product_id = 101;

The changes happen in the real products table!

Requirements for Updatable Views

Your view must follow these rules:

WITH CHECK OPTION

What if someone inserts a product with is_active = 0 through our active_products view?

It would go into the table but disappear from the view! Confusing!

Solution:

CREATE VIEW active_products AS
SELECT product_id, name, price
FROM products
WHERE is_active = 1
WITH CHECK OPTION;

Now if someone tries to insert inactive products through this view, SQL says “NOPE!” 🚫

4️⃣ View Restrictions

What Views CAN’T Do

Even magic has limits! Here are the boundaries:

❌ No Indexes on Views

-- This WON'T work!
CREATE INDEX idx ON my_view(column);

Views are virtual — there’s no actual data to index.

❌ ORDER BY Limitations

-- Usually NOT allowed in view definition
CREATE VIEW sorted_books AS
SELECT * FROM books
ORDER BY title;  -- ❌ Most databases reject this

Why? Because you should ORDER when you use the view:

SELECT * FROM my_view
ORDER BY column;  -- ✅ Do it here

❌ Can’t Use Certain Clauses

Views typically can’t include:

• INTO clause
• LIMIT / TOP (varies by database)
• Temporary tables
• User variables

❌ Nested View Limits

-- View on view on view on view...
CREATE VIEW v4 AS SELECT * FROM v3;
CREATE VIEW v3 AS SELECT * FROM v2;
CREATE VIEW v2 AS SELECT * FROM v1;
-- Most databases limit nesting depth!

Too many layers = slow and confusing.

❌ Circular References

-- View A uses View B
-- View B uses View A
-- 💥 IMPOSSIBLE! Infinite loop!

Performance Considerations

Views don’t make queries faster by magic:

graph TD
    A["You Query View"] --> B["Database Runs<br>Underlying Query"]
    B --> C["Results Returned"]
    D["View Definition"] -.saved.-> B

Complex views = Complex queries = Slower results

Summary Table

🎯 Quick Recap

graph TD
    A["VIEW"] --> B["Virtual Table"]
    B --> C["Saved SELECT Query"]

    A --> D["CREATE VIEW name AS..."]

    A --> E["Updatable?"]
    E --> F["Simple = YES"]
    E --> G["Complex = NO"]

    A --> H["Restrictions"]
    H --> I["No Indexes"]
    H --> J["No ORDER BY in def"]
    H --> K["Nesting Limits"]

🌟 Your Superpower

You now understand SQL Views! You can:

✅ Create views to save complex queries ✅ Use views like regular tables ✅ Update data through simple views ✅ Protect data with restricted views ✅ Recognize view limitations

Views are like having a personal assistant who remembers your favorite questions and answers them instantly!

Remember: A VIEW is your magic window. It doesn’t hold the books — it just shows you exactly what you want to see! 🪟✨