CREATE VIEW in SQL with Simple and JOIN-Based Examples

Creating a view is as simple as prefacing a normal SELECT query with CREATE VIEW and a name. This lesson covers the exact syntax, from the simplest single-table view to a view built on a multi-table JOIN, along with the CREATE OR REPLACE pattern used to safely update an existing view's definition.

Key Definitions

• CREATE VIEW: The SQL statement used to define a new view, specifying its name and the underlying SELECT query it represents.
• CREATE OR REPLACE VIEW: A variant that redefines an existing view's underlying query if it already exists, or creates it fresh if it doesn't, avoiding a manual DROP VIEW step first.

What You'll Learn

• Write the CREATE VIEW syntax for a simple single-table view.
• Write the CREATE VIEW syntax for a complex JOIN-based view.
• Use CREATE OR REPLACE VIEW to safely modify an existing view's definition.
• Apply sensible naming conventions for views.

Detailed Explanation

The basic syntax is `CREATE VIEW view_name AS SELECT ...`, where the SELECT statement can be anything from a trivial single-table filter to a deeply nested, multi-table JOIN with aggregation. A simple view might just restrict which columns and rows of the doctors table are visible: `CREATE VIEW doctor_directory_view AS SELECT doctor_name, department_id FROM doctors`, deliberately excluding the salary column entirely.

A more complex, JOIN-based view combines multiple tables into one convenient, reusable structure, exactly as demonstrated in the previous lesson's completed_appointments_view. Views can include WHERE clauses, JOINs, GROUP BY, and virtually any construct a normal SELECT query supports, though certain constructs (like GROUP BY) affect whether the resulting view can later be used for INSERT or UPDATE, a distinction covered in Lesson 9.3.

When a view's definition needs to change — perhaps to add a new column or adjust a filter — using `CREATE OR REPLACE VIEW view_name AS ...` updates the existing view's definition directly, without needing to first run a separate DROP VIEW statement. This is both more convenient and safer, since it avoids a brief window where the view doesn't exist at all between a drop and a fresh create. A sensible naming convention, such as consistently suffixing view names with _view, helps future developers immediately recognize which database objects are views versus physical tables.

Visual Summary

Two side-by-side syntax templates. Left: [CREATE VIEW simple_view_name AS SELECT col1, col2 FROM single_table WHERE condition;] labeled 'Simple single-table view'. Right: [CREATE VIEW complex_view_name AS SELECT ... FROM table_a JOIN table_b ON ... JOIN table_c ON ...;] labeled 'JOIN-based view combining multiple tables'.

Quick Reference

View Type	Example Use Case
Simple single-table view	Restricting visible columns, like hiding salary from a doctor_directory_view
JOIN-based view	Combining appointments, patients, and doctors into one reusable structure
CREATE OR REPLACE VIEW	Safely updating an existing view's definition without a separate DROP step

SQL Example

CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

-- Simple single-table view: hides the sensitive salary column
CREATE VIEW doctor_directory_view AS
SELECT doctor_id, doctor_name, department_id
FROM doctors;

-- JOIN-based view: a reusable, combined appointment summary
CREATE VIEW appointment_summary_view AS
SELECT
  a.appointment_id, p.patient_name, d.doctor_name,
  dept.department_name, a.appointment_date, a.status
FROM appointments a
JOIN patients p ON a.patient_id = p.patient_id
JOIN doctors d ON a.doctor_id = d.doctor_id
JOIN departments dept ON d.department_id = dept.department_id;

-- Safely update the simple view's definition to add a new column
CREATE OR REPLACE VIEW doctor_directory_view AS
SELECT doctor_id, doctor_name, department_id, 'Active' AS status
FROM doctors;

The doctor_directory_view deliberately excludes the salary column, providing a safe, restricted view of doctor data. The appointment_summary_view combines four tables into one reusable structure. The final CREATE OR REPLACE VIEW statement updates doctor_directory_view's definition to add a new computed status column, without needing to separately drop the original view first — any queries already referencing doctor_directory_view will now see this updated structure going forward.

Real-World Examples

• HR systems commonly create restricted views of employee tables that expose names and departments but exclude salary and personal contact information from general company-wide access.
• Reporting teams routinely create JOIN-based views for common cross-departmental reports, avoiding the need for every analyst to independently write and maintain the same complex JOIN.
• Database migrations often use CREATE OR REPLACE VIEW to evolve a view's structure incrementally as application requirements change, without breaking existing dependent queries during the transition.

Common Mistakes to Avoid

• Using plain CREATE VIEW on a name that already exists, causing an error instead of using CREATE OR REPLACE VIEW.
• Not adopting a consistent naming convention for views, making it hard to distinguish views from physical tables at a glance.
• Building an overly complex view with excessive nested logic that becomes difficult to maintain or debug later.

Interview Questions

Q1. What is the basic syntax for creating a SQL view?

CREATE VIEW view_name AS, followed by a SELECT statement defining what data the view represents. This SELECT statement can be as simple as a single-table filter or as complex as a multi-table JOIN with aggregation.

Q2. What is the advantage of using CREATE OR REPLACE VIEW instead of DROP VIEW followed by CREATE VIEW?

CREATE OR REPLACE VIEW updates the view's definition directly in a single statement, avoiding a brief window where the view doesn't exist at all between a separate DROP and CREATE step, which is both more convenient and safer for dependent queries.

Practice MCQs

1. What follows the AS keyword in a CREATE VIEW statement?

1. A table name only
2. A SELECT statement defining the view's data
3. A list of column data types
4. An INSERT statement

Answer: B. A SELECT statement defining the view's data

Explanation: The AS keyword in CREATE VIEW is followed by the SELECT query that defines what data the view will represent when queried.

2. What does CREATE OR REPLACE VIEW do if the view doesn't already exist?

1. It throws an error
2. It creates the view fresh, just like a normal CREATE VIEW
3. It silently does nothing
4. It only works on tables, not views

Answer: B. It creates the view fresh, just like a normal CREATE VIEW

Explanation: CREATE OR REPLACE VIEW creates the view if it doesn't exist yet, or updates its definition if it already does, in either case without requiring a separate DROP step.

Quick Revision Points

• CREATE VIEW view_name AS SELECT ... is the standard syntax.
• CREATE OR REPLACE VIEW safely updates an existing view's definition in one statement.
• A consistent naming convention (like a _view suffix) is a widely recommended best practice.

Conclusion

• CREATE VIEW defines a new view using a standard SELECT statement.
• Views can range from simple single-table filters to complex multi-table JOINs.
• CREATE OR REPLACE VIEW safely updates an existing view's definition without a separate DROP step.

Creating a SQL view follows the simple pattern CREATE VIEW view_name AS SELECT ..., where the underlying SELECT statement can be a simple single-table filter — often used to restrict visible columns for security — or a complex multi-table JOIN combining data for convenient reuse. When a view's definition needs updating, CREATE OR REPLACE VIEW safely redefines it in a single statement, avoiding the brief gap that a separate DROP VIEW followed by CREATE VIEW would introduce.