Creating Views

A view is a virtual table defined by a stored SELECT query that presents data from one or more tables without physically storing it.

A view is essentially a named query stored in the database that behaves like a table when queried. It does not store data itself. Instead, it dynamically retrieves data from the underlying tables. A view always reflects the most current data in the database.

Creating Views In the Query Builder

A view must be created before it can be used. Views are created using CREATE VIEW or CREATE OR REPLACE VIEW.

• CREATE VIEW creates a new view with the provided name if it does not yet exist. If a view with the given name already exists, an error is returned.
• CREATE OR REPLACE VIEW creates a view with the given name if it does not yet exist; otherwise, it overwrites the currently stored view.

📌 CREATE OR REPLACE VIEW overwrites stored views without warning. This action cannot be undone. Restoring the old version would require an import or restore.

✨ Example: Creating a View

Create view my_employees: select all employees working for the sales department (deptno = 30).

CREATE OR REPLACE VIEW `my_employees` AS
SELECT *
FROM emp
WHERE deptno = 30

✨ Example: Testing a View

To test the view from the previous section, simply add a comment before the first line. This prevents the view from being created or replaced and allows you to test the query.

-- CREATE OR REPLACE VIEW `my_employees` AS
SELECT *
FROM emp
WHERE deptno = 30

✨ Example: Using a View

After creating view my_employees, it can be used in a SELECT query just like a table.

select *
from `my_employees`