Views - Learning Module

Loading content...

0/241

CREATE VIEW: Syntax and Implementation

From Concept to Implementation

With a solid understanding of what views are and why they matter, it's time to create them. The CREATE VIEW statement is your gateway to defining virtual tables that encapsulate complex queries, enforce security boundaries, and simplify data access patterns.

While the basic syntax is straightforward, production-grade view creation involves nuanced decisions: naming conventions, column specifications, schema placement, and DBMS-specific options that affect behavior and performance.

This page provides complete mastery of the CREATE VIEW statement—from basic syntax to advanced options, from standard SQL to vendor extensions.

What You Will Learn

By the end of this page, you will be able to create views using proper syntax, understand DBMS-specific variations, apply naming conventions, handle edge cases, and leverage advanced options like SCHEMABINDING and WITH CHECK OPTION.

Basic CREATE VIEW Syntax

The fundamental CREATE VIEW syntax is remarkably consistent across all major relational databases, following the SQL standard closely:

CREATE VIEW view_name [(column_list)]
AS
    select_statement;

Let's break down each component:

CREATE VIEW: The DDL command that signals view creation.

view_name: A unique identifier within the schema. Naming conventions typically use prefixes like v_, vw_, or view_ to distinguish views from tables.

column_list: Optional. Explicit names for the view's columns. If omitted, column names are derived from the SELECT clause.

AS: Keyword separating the view name from its definition.

select_statement: Any valid SELECT statement that defines the view's content.

basic_create_view.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Minimal view: inherits column names from SELECT
CREATE VIEW v_active_employees AS
    SELECT id, first_name, last_name, email, department_id, hire_date
    FROM employees
    WHERE termination_date IS NULL;
 
-- With explicit column list: renames/aliases columns
CREATE VIEW v_employee_summary (
    employee_id,
    full_name,
    email_address,
    department_code
) AS
    SELECT 
        e.id,
        CONCAT(e.first_name, ' ', e.last_name),
        e.email,
        d.code
    FROM employees e
    JOIN departments d ON e.department_id = d.id;
 
-- Complex view with aggregation
CREATE VIEW v_department_metrics AS
    SELECT 
        d.name AS department_name,
        COUNT(e.id) AS total_employees,
        COUNT(CASE WHEN e.termination_date IS NULL THEN 1 END) AS active_employees,
        AVG(e.salary) AS average_salary,
        MIN(e.hire_date) AS earliest_hire,
        MAX(e.hire_date) AS latest_hire
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    GROUP BY d.id, d.name;

When to Use Explicit Column Lists

Always use explicit column lists when: (1) your SELECT includes expressions or aggregations, (2) you want to rename columns from base tables, (3) you're joining tables with same-named columns, or (4) you need documentation clarity for future maintainers.

Column Specification Rules

Column naming in views follows strict rules that, if violated, cause compilation errors:

Rule 1: Every column must have a name

Expressions like salary * 12 or CONCAT(first, last) produce unnamed results. You must alias them:

-- ERROR: unnamed column
CREATE VIEW v_bad AS SELECT salary * 12 FROM employees;

-- CORRECT: named via AS
CREATE VIEW v_good AS SELECT salary * 12 AS annual_salary FROM employees;

Rule 2: Column names must be unique

If joining tables with identically named columns, you must alias to avoid ambiguity:

-- ERROR: duplicate 'name' column
CREATE VIEW v_bad AS 
    SELECT e.name, d.name FROM employees e JOIN departments d ...;

-- CORRECT: disambiguated
CREATE VIEW v_good AS 
    SELECT e.name AS employee_name, d.name AS department_name ...;

Column Naming Requirements
Scenario	Problem	Solution
Scalar expression	`SUM(salary)` has no name	Use `SUM(salary) AS total_salary`
Concatenation	`first \|\| last` unnamed	Use `first \|\| last AS full_name`
Duplicate names	Two `id` columns from JOIN	Alias as `employee_id`, `dept_id`
Function result	`UPPER(name)` unnamed	Use `UPPER(name) AS name_upper`
Subquery column	Scalar subquery result	Alias the subquery result
CASE expression	CASE ... END unnamed	Use `CASE ... END AS status`

Rule 3: Column list count must match SELECT clause

If you specify an explicit column list, the number of names must exactly match the number of expressions in SELECT:

-- ERROR: 3 names, 4 columns in SELECT
CREATE VIEW v_mismatch (a, b, c) AS
    SELECT col1, col2, col3, col4 FROM t;

-- CORRECT: matching counts
CREATE VIEW v_match (a, b, c, d) AS
    SELECT col1, col2, col3, col4 FROM t;

Best practice: Prefer explicit column lists for documentation and stability. When base tables evolve, explicit lists make the view's interface clear.

CREATE OR REPLACE VIEW

Modifying an existing view is a common operation during development and maintenance. Most DBMSs support the CREATE OR REPLACE VIEW syntax, which creates a new view or updates an existing one atomically:

CREATE OR REPLACE VIEW view_name AS
    new_select_statement;

This approach offers significant advantages over DROP + CREATE:

Atomicity: The view is updated in a single transaction—there's never a moment when the view doesn't exist.

Permission preservation: Grants and permissions on the view are retained. With DROP + CREATE, you'd need to re-grant all permissions.

Dependency continuity: Objects depending on the view remain valid (assuming the new definition is compatible).

create_or_replace.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Original view
CREATE VIEW v_customer_orders AS
    SELECT c.id, c.name, COUNT(o.id) AS order_count
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name;
 
-- Evolution: add total revenue to the view
CREATE OR REPLACE VIEW v_customer_orders AS
    SELECT 
        c.id, 
        c.name, 
        COUNT(o.id) AS order_count,
        COALESCE(SUM(o.total_amount), 0) AS total_revenue  -- New column
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name;
 
-- PostgreSQL/Oracle: Full support for CREATE OR REPLACE
-- SQL Server: Use ALTER VIEW instead (see below)
-- MySQL: Full support for CREATE OR REPLACE

Column Compatibility

While CREATE OR REPLACE preserves permissions, it may break dependent objects if you change column names or types. Adding columns at the end is usually safe; removing columns or changing their positions can break queries that rely on the old structure.

SQL Server alternative: ALTER VIEW

SQL Server does not support CREATE OR REPLACE. Instead, use ALTER VIEW:

-- SQL Server: Modify existing view
ALTER VIEW v_customer_orders AS
    SELECT c.id, c.name, COUNT(o.id) AS order_count,
           SUM(o.total_amount) AS total_revenue
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name;

ALTER VIEW offers the same atomicity and permission preservation as CREATE OR REPLACE.

WITH CHECK OPTION

When a view is updatable (we'll explore this in depth later), users can INSERT and UPDATE rows through the view. But what happens if an INSERT creates a row that doesn't satisfy the view's WHERE clause? Without constraints, the row is created in the base table but invisible through the view—a confusing and often unintended result.

WITH CHECK OPTION prevents this by ensuring that all modifications through the view satisfy the view's defining predicate:

CREATE VIEW view_name AS
    SELECT ... FROM ... WHERE condition
WITH CHECK OPTION;

with_check_option.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- View showing only active employees
CREATE VIEW v_active_employees AS
    SELECT id, name, email, status
    FROM employees
    WHERE status = 'active'
WITH CHECK OPTION;
 
-- Attempt to insert an inactive employee through the view
INSERT INTO v_active_employees (name, email, status)
VALUES ('John Doe', 'john@example.com', 'inactive');
-- ERROR: View WITH CHECK OPTION violation
 
-- Attempt to update an active employee to inactive
UPDATE v_active_employees
SET status = 'inactive'
WHERE id = 123;
-- ERROR: View WITH CHECK OPTION violation
 
-- Valid operations that satisfy the check:
INSERT INTO v_active_employees (name, email, status)
VALUES ('Jane Smith', 'jane@example.com', 'active');
-- SUCCESS: row is visible through view
 
UPDATE v_active_employees
SET email = 'jane.new@example.com'
WHERE id = 124;
-- SUCCESS: status remains 'active'

CASCADED vs LOCAL check options:

When views are built on other views, check option behavior can be specified more precisely:

WITH CASCADED CHECK OPTION: Checks are applied at all levels—the current view AND all underlying views (default in most DBMSs)
WITH LOCAL CHECK OPTION: Only the current view's WHERE clause is checked, not underlying views

-- Base view with no check
CREATE VIEW v_employees AS SELECT * FROM employees;

-- Derived view with local check
CREATE VIEW v_active_local AS
    SELECT * FROM v_employees WHERE status = 'active'
WITH LOCAL CHECK OPTION;

-- Derived view with cascaded check (stricter)
CREATE VIEW v_active_cascaded AS
    SELECT * FROM v_employees WHERE status = 'active'
WITH CASCADED CHECK OPTION;

Use WITH CHECK OPTION for Data Integrity

Whenever you create an updatable view with filtering conditions, strongly consider adding WITH CHECK OPTION. It prevents the confusing scenario where you insert a row and immediately cannot see it through the same view.

Schema Binding and Dependencies

Standard views maintain loose coupling with base tables—you can modify or drop base objects, potentially breaking views silently. Schema binding creates tight coupling that prevents accidental breakage.

SQL Server: WITH SCHEMABINDING

SQL Server's SCHEMABINDING option binds the view to the schema of referenced objects:

CREATE VIEW v_employee_summary
WITH SCHEMABINDING
AS
    SELECT id, name, salary
    FROM dbo.employees;  -- Must use schema-qualified names

With SCHEMABINDING:

Referenced tables cannot be dropped
Referenced columns cannot be altered or dropped
Ensures view remains valid throughout its lifetime
Required for creating indexed views in SQL Server

Schema Binding Behavior Across DBMSs
DBMS	Feature	Syntax	Effect
SQL Server	SCHEMABINDING	`WITH SCHEMABINDING`	Prevents base object modifications
Oracle	Object dependencies	Automatic tracking	Views marked invalid on changes
PostgreSQL	Dependencies	Automatic tracking	DROP CASCADE required for dependencies
MySQL	DEFINER security	`SQL SECURITY DEFINER`	Runs with definer's privileges

schemabinding_example.sql

-- SQL Server: Create schema-bound view
CREATE VIEW dbo.v_product_inventory
WITH SCHEMABINDING
AS
    SELECT 
        p.product_id,
        p.product_name,
        i.quantity_on_hand,
        i.last_restocked
    FROM dbo.products p
    JOIN dbo.inventory i ON p.product_id = i.product_id;
 
-- This now FAILS because view is schema-bound
ALTER TABLE dbo.products DROP COLUMN product_name;
-- Error: Cannot DROP COLUMN because it is referenced by view 'v_product_inventory'
 
-- Must drop or alter the view first
DROP VIEW dbo.v_product_inventory;
ALTER TABLE dbo.products DROP COLUMN product_name;  -- Now succeeds

When to Use Schema Binding

Use schema binding for critical views that applications depend upon, views that will be indexed (SQL Server requirement), and views where accidental base table changes would cause production incidents. The slight inconvenience of explicit drops is worthwhile for stability.

View Security Options

Views have become a primary mechanism for implementing security in databases. Several options control how security context is applied when querying views:

SQL SECURITY DEFINER vs INVOKER:

This option determines whose privileges are used when executing the view's query:

DEFINER: The view runs with the privileges of the user who created it
INVOKER: The view runs with the privileges of the user querying it

-- MySQL/PostgreSQL: Security definer (default behavior varies)
CREATE SQL SECURITY DEFINER VIEW v_all_salaries AS
    SELECT name, salary FROM employees;

-- User without access to employees table can still query this view
-- if they have SELECT on the view, because it runs as the definer

security_options.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- MySQL: Explicit security specification
CREATE 
    DEFINER = 'admin'@'localhost'      -- Who created/owns the view
    SQL SECURITY DEFINER               -- Use definer's privileges
VIEW v_public_employee_data AS
    SELECT id, name, department, hire_date
    FROM employees
    WHERE public_profile = TRUE;
 
-- Oracle: Bequeath option
CREATE OR REPLACE VIEW v_my_data 
BEQUEATH DEFINER                       -- Oracle's equivalent
AS
    SELECT * FROM sensitive_data
    WHERE owner = SYS_CONTEXT('USERENV', 'SESSION_USER');
 
-- SQL Server: VIEW_METADATA option
CREATE VIEW v_report
WITH VIEW_METADATA                     -- Exposes view metadata to clients
AS
    SELECT product_name, SUM(quantity) AS total
    FROM orders
    GROUP BY product_name;

Security implications:

DEFINER mode enables privilege escalation by design. An admin can create a view over sensitive tables, then grant SELECT on the view to users who cannot access those tables directly. The view acts as a controlled window into the data.

INVOKER mode requires users to have privileges on all underlying objects. This is more restrictive but prevents unintended privilege escalation.

Best practice: Use DEFINER security for views that intentionally expose limited data to less-privileged users. Use INVOKER security when views are convenience abstractions and users should already have underlying access.

Security Audit Considerations

DEFINER-mode views can create security audit blind spots. When a user queries through a DEFINER view, database logs may show the definer's credentials accessing base tables, not the actual user's. Ensure your security and audit policies account for this indirection.

View Naming Conventions

Consistent naming conventions are crucial for database maintainability. Views should be immediately distinguishable from tables, and their names should communicate purpose.

Common prefixing strategies:

Prefix	Meaning	Example
`v_`	General view	`v_active_users`
`vw_`	Alternative prefix	`vw_order_summary`
`vi_`	Indexed/materialized view	`vi_sales_by_region`
`vr_`	Reporting view	`vr_monthly_revenue`
`vs_`	Security/filtered view	`vs_user_own_data`

Naming Best Practices

•Use a consistent prefix for all views (v_, vw_) to distinguish from tables at a glance
•Make names descriptive — v_monthly_sales_by_region is better than v_sales1
•Indicate filtering — Include terms like active, pending, archived when views filter by status
•Indicate aggregation — Use summary, totals, metrics for aggregating views
•Match schema organization — Place views in appropriate schemas (e.g., reporting.v_dashboard_metrics)
•Document in the name — v_customers_with_orders_last_30_days is self-documenting

naming_examples.sql

-- Anti-patterns: Poor naming
CREATE VIEW view1 AS ...;                    -- Meaningless
CREATE VIEW employees_view AS ...;           -- Redundant suffix
CREATE VIEW get_active AS ...;               -- Verb prefix confusing
 
-- Good patterns: Clear, consistent naming
CREATE VIEW v_active_employees AS ...;       -- Prefix + status + entity
CREATE VIEW v_order_summary_by_customer AS...;  -- Describes content
CREATE VIEW v_products_low_stock AS ...;     -- Indicates filtering
CREATE VIEW v_monthly_revenue_ytd AS ...;    -- Time-bounded aggregation
 
-- Schema-organized views
CREATE VIEW reporting.v_executive_dashboard AS ...;
CREATE VIEW security.v_user_accessible_records AS ...;
CREATE VIEW analytics.v_customer_lifetime_value AS ...;

Team Consistency Matters

Whatever convention you choose, document it and enforce it through code review. The specific prefix (v_ vs vw_) matters less than the consistency. A codebase where some views use v_, others use vw_, and some have no prefix at all is harder to navigate than any consistent approach.

Summary: Mastering CREATE VIEW

You now have comprehensive knowledge of the CREATE VIEW statement and its variations. Let's consolidate the essential points:

Key Takeaways

•Basic syntax is universal — CREATE VIEW name AS select_statement works across all major DBMSs.
•Column specification requires precision — All expressions need names, and counts must match when using explicit column lists.
•CREATE OR REPLACE enables safe evolution — Atomically update views while preserving permissions (use ALTER VIEW in SQL Server).
•WITH CHECK OPTION enforces consistency — Prevents INSERT/UPDATE through views that would create invisible rows.
•Schema binding prevents accidental breakage — Lock views to their base structures for critical, indexed views.
•Security options control privilege flow — DEFINER vs INVOKER determines whose permissions apply.
•Naming conventions ensure maintainability — Prefix views consistently and make names self-documenting.

What's next:

With creation syntax mastered, the next page explores why views matter—their strategic benefits for security, abstraction, simplification, and maintenance. Understanding these benefits helps you identify opportunities to leverage views in your database designs.

Page Complete

You can now create views with confidence—handling column naming, options, security settings, and naming conventions. Next, we'll explore the strategic benefits that make views indispensable in production systems.