Views & Aliases — SQL

A view is a SELECT query saved under a name. CREATE VIEW name AS SELECT ... saves the query, and SELECT * FROM name runs it, just like reading a table. A view…

Open the "Most played" playlist in your music app this week, then open it again next month. Its contents have changed, though you never edited it. That playlist doesn't store a fixed list of songs. It only stores its rule: the songs you play most. Each time you open the playlist, the rule runs again, so its contents always follow your latest listening.

A view works like that playlist, but in a database. A view is a SELECT query saved under a name, which you can then call like a table: you write its name in FROM. The query is the playlist's rule, and the rows it returns are the songs. Only the query is stored. The data comes from the real tables each time the view is called.

The tables we use

There are two tables from the exercise. The customers table holds Budi (id 1), Siti (2) and Andi (3). The orders table holds four orders, and customer_id points at the customer who placed each one:

id customer_id product amount
1 1 Laptop 1200
2 1 Mouse 15
3 2 Monitor 350
4 2 Keyboard 50

Andi hasn't ordered anything yet.

Make a view with CREATE VIEW

A summary of each customer's spending (how many orders, and how much in total) needs JOIN, COUNT, SUM and GROUP BY. Instead of writing that long query on every page of your app, save the query once as a view:

CREATE VIEW order_summary AS
SELECT
  customers.name AS customer_name,
  COUNT(orders.id) AS order_count,
  SUM(orders.amount) AS total_spent
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name;

After the view is made, that long query is called by its name:

SELECT * FROM order_summary;
customer_name order_count total_spent
Budi 2 1215
Siti 2 400

As in the INNER JOIN lesson, tables can have aliases too. FROM customers c INNER JOIN orders o ON c.id = o.customer_id makes c and o short names for customers and orders. So c.name is the same as customers.name: the name column of the customers table.

A view stores a query, not data

Andi orders a Headset. Guess first: for Andi to appear in order_summary, does the view need to be made again?

INSERT INTO orders (customer_id, product, amount) VALUES (3, 'Headset', 75);
SELECT * FROM order_summary;
customer_name order_count total_spent
Budi 2 1215
Siti 2 400
Andi 1 75

No need: Andi appears right away, without making the view again. Every time you run a SELECT on a view, the database runs the saved query again, on the tables as they are at that moment. For order_summary, the database stores only the text of the query from the CREATE VIEW above, not any rows.

Because a view has no data of its own, a view in SQLite can't be filled directly with INSERT either:

INSERT INTO order_summary VALUES ('Dewi', 1, 100);
-- cannot modify order_summary because it is a view

To change what a view shows, change the real tables: customers or orders. In MySQL and PostgreSQL, a simple view that reads one table accepts INSERT (the row goes into that table), but a view with GROUP BY and COUNT such as order_summary is still refused.

Changing and deleting a view

DROP VIEW deletes a view. No data in the real tables is deleted with it, because that data was never in the view:

DROP VIEW order_summary;
DROP VIEW IF EXISTS order_summary;  -- no error if the view is already gone

Making a view with a name that's already taken gives the error view order_summary already exists. So to change a view's query in SQLite, first delete the view with DROP VIEW, then run CREATE VIEW again. MySQL and PostgreSQL have the shortcut CREATE OR REPLACE VIEW (make the view, or replace it if it exists), but SQLite rejects it with near "OR": syntax error.

📌 Why use a view

⚠️ Why won't the view exercise pass?

TL;DR: A view is a SELECT query saved under a name. CREATE VIEW name AS SELECT ... saves the query, and SELECT * FROM name runs it, just like reading a table. A view stores a query, not data: changes to the real tables appear in the view right away, and in SQLite you can't INSERT into a view (fill it directly). AS gives an alias, a new heading, to a column (COUNT(orders.id) AS order_count). A table can also get an alias, a short name (customers c). Delete a view with DROP VIEW. To replace a view in SQLite, run DROP VIEW then CREATE VIEW, because CREATE OR REPLACE VIEW only exists in MySQL and PostgreSQL.

⚡ Save a query under a name

Write:

CREATE VIEW order_summary AS
SELECT
  customers.name AS customer_name,
  COUNT(orders.id) AS order_count,
  SUM(orders.amount) AS total_spent
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name;

Meaning: CREATE VIEW makes a view: a SELECT query saved under a name, here order_summary. The query doesn't run now, it's only saved. An alias like AS order_count gives a result column a new heading.

The exercise checks the three columns (customer_name, order_count, total_spent) and the INNER JOIN.

⚡ Call it like a table

Write:

SELECT * FROM order_summary;

Meaning: The view goes in FROM, the same as a table. The result is 2 rows: the customers Budi and Siti. The third customer, Andi, hasn't ordered anything, so the INNER JOIN doesn't include him.

⚡ It stores a query, not data

Guess first: Andi orders a Headset. Does the view have to be made again for Andi to appear in order_summary?

No need. Every time it's called, the view runs its query again on the tables as they are now, so Andi appears right away.

Meaning: A view stores only a query and has no data of its own. That's why, in SQLite, you can't INSERT into a view either. To change what it shows, change the real tables.

⚡ Replacing a view in SQLite

Write:

DROP VIEW IF EXISTS order_summary;

Meaning: Making a view with a name that's taken ends in an already exists error. So to change a view's query in SQLite, first delete the view with DROP VIEW, then run CREATE VIEW again. IF EXISTS stops DROP VIEW from giving an error when the view doesn't exist.

What you will learn