🎓 A1Learner
🔍

SQL Views

A view is a saved query that reads like a table. CREATE VIEW stores the query under a name.

Save the query

This view keeps the name and city of each customer in the USA. The statement returns no rows.

CREATE VIEW usa_customers AS
SELECT name, city FROM customers WHERE country = 'USA';

Read the view

The view returns only customers from the USA. ORDER BY sorts its rows like any table's rows.

CREATE VIEW usa_customers AS
SELECT name, city FROM customers WHERE country = 'USA';
SELECT name, city FROM usa_customers ORDER BY name;

Example

CREATE VIEW usa_customers AS
SELECT name, city FROM customers WHERE country = 'USA';
SELECT city, COUNT(*) AS customers FROM usa_customers GROUP BY city;

Key points

  • CREATE VIEW: the statement that saves a query under a name
  • A view stores a query, not a copy of the rows.
  • Use a view in a SELECT like a table.
  • Reading a view runs its saved query.
  • DROP VIEW removes a view.

In this example, CREATE VIEW saves the USA customer query as usa_customers. The SELECT reads the view and groups its rows by city. Both customers live in Austin, so the count is 2.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

What does CREATE VIEW store?