Master

A saved question

The same five-line join sat in four places in my code, and when the shape changed I had to find and fix all four. A view is that question stored once under a name.

What you will see

name|city Ada L.|Rome Grace H.|Paris sqlite>

About 2 min of reading

Try It

Save a join as a view and read it, then confirm it tracks the live table.

Save the users-and-cities join under the name placed.

CREATE VIEW placed AS SELECT name, city FROM users JOIN cities ON users.id = cities.id;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

Read the view. It returns the join without you writing it again.

SELECT * FROM placed;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

name|city Ada|Rome Grace H.|Paris sqlite>

Change the underlying table, then read the view again. The view shows the new value, proving it stores the question not the answer.

UPDATE users SET name = "Ada L." WHERE id = 1; SELECT * FROM placed;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

name|city Ada L.|Rome Grace H.|Paris sqlite>

Data Flow

CREATE VIEW stores a query under a name, and reading the view runs that saved query against the live table each time.

1 CREATE VIEW stores a query

↓

2 You read the view name

↓

3 Saved query runs on live data

↓

4 Answer reflects current rows

Why this works

A view is a question

A view stores a query under a name. It is not a new table and it holds no rows of its own. Every time you read the view, the database runs the saved query against the current data. That is why a view can never be stale, and why it costs nothing to keep one.

Views and the join

The common use is a join you ask over and over. Save it once as a view and every caller reads the view instead of repeating the join. When the join changes, you change one CREATE VIEW and every caller follows. This is the same reason you name a constant instead of pasting a number.

Examples

Name a query

CREATE VIEW active AS SELECT name FROM users WHERE id > 1;

CREATE VIEW gives a query a name. From here active behaves like a table you can read from, and it always reflects the current data.

Read it like a table

SELECT * FROM active;

You did not rewrite the WHERE. The view carries it. The answer is exactly what the saved query returns against the live table.

It is not a copy

UPDATE users SET name = "A." WHERE id = 2;

Change the table and the view changes with it. A view stores the question, not the answer, so it is never out of date.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Tables that vouch for each other

Previous Lesson

Tables that vouch for each other

Next Lesson

All of it or none of it

All of it or none of it →