How to Create a View in SQL

Use a view when a complex query should become a reusable virtual table. Views are perfect for dashboards, shared reporting logic, security layers, and simplifying repeated joins.

What you’ll build or solve

You’ll learn how to create a view in SQL and query it like a normal table. You’ll also know when a view is better than repeating raw query logic.

When this approach works best

This approach is the right choice when the same query logic is reused across multiple dashboards, reports, or teams.

Common real-world scenarios include:

• Revenue reporting views
• Active user dashboards
• Joined customer profiles
• Security-safe filtered datasets
• BI tool data sources

This is a bad idea when the logic is temporary and used only once.

Prerequisites

You only need:

• Basic SELECT
• A reusable query worth saving
• Permission to create database objects

Step-by-step instructions

Step 1: Wrap the query with CREATE VIEW

Start with the query you want to reuse.

CREATE VIEW paid_orders AS
SELECT order_id, user_id, total
FROM orders
WHERE status = 'paid';

This creates a virtual table named paid_orders.

Query it like a normal table.

SELECT *
FROM paid_orders;

The underlying data stays live because the view runs the saved query logic.

What to look for:

• A view stores query logic, not copied data
• Query it like a normal table
• Great for shared business logic
• Perfect for dashboards and BI
• Underlying table changes affect the view immediately

Examples you can copy

Active users

CREATE VIEW active_users AS
SELECT *
FROM users
WHERE last_login >= CURRENT_DATE - INTERVAL '30 days';

Revenue by country

CREATE VIEW revenue_by_country AS
SELECT country, SUM(total) AS revenue
FROM orders
GROUP BY country;

Joined customer profiles

CREATE VIEW customer_profiles AS
SELECT u.email, p.bio
FROM users u
LEFT JOIN profiles p
ON u.user_id = p.user_id;

Common mistakes and how to fix them

Mistake 1: Expecting stored snapshot data

What the reader might do:

Assume the view freezes the result at creation time.

Why it breaks: standard views always reflect current underlying data.

Corrected approach:

Use materialized views if snapshots are needed.

Mistake 2: Hiding overly complex logic in many nested views

What the reader might do:

Create views on top of views on top of views.

Why it breaks: debugging becomes harder.

Corrected approach:

Keep view layers intentional and documented.

Mistake 3: Using SELECT * in unstable schemas

What the reader might do:

CREATE VIEW my_view AS
SELECT *
FROM users;

Why it breaks: schema changes can unexpectedly alter downstream outputs.

Corrected approach:

List exact columns.

Troubleshooting

If the view errors after schema changes, update the selected columns.

If performance drops, consider indexes on base tables.

If snapshots are required, use materialized views.

If BI logic is reused widely, centralize it in one documented view.

Quick recap

• Views save reusable query logic
• Query them like normal tables
• They always reflect live underlying data
• Great for dashboards and BI
• Use exact columns for stability