SQL Views

An interesting thing you can do with SQL is to create a view.

A view is like a table, except instead of being a real table, on its own, it is dynamically built by the result of a SELECT query.

Views exist to give a name to a query you keep repeating. Instead of pasting the same join into every report, you define it once and query the view like any other table. It also hides complexity: whoever reads from the view doesn’t need to know how the join works.

Let’s use the example we used in the joins lesson:

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL PRIMARY KEY
);

CREATE TABLE cars (
  brand CHAR(20) NOT NULL,
  model CHAR(20) NOT NULL,
  owner CHAR(20) NOT NULL PRIMARY KEY
);

We add some data:

INSERT INTO people VALUES (37, 'Flavio');
INSERT INTO people VALUES (8, 'Roger');
INSERT INTO cars VALUES ('Ford', 'Fiesta', 'Flavio');
INSERT INTO cars VALUES ('Ford', 'Mustang', 'Roger');

We can create a view that we call car_age that always contains the correlation between a car model and its owner’s age:

CREATE VIEW car_age AS SELECT model, age AS owner_age FROM people JOIN cars ON people.name = cars.owner;

Here is the result we can inspect with SELECT * FROM car_age:

        model         | owner_age 
----------------------+-----------
 Fiesta               |        37
 Mustang              |         8

The word “always” matters here. A view stores the query, not the data. Every time you select from it, the database runs the underlying SELECT again, so the result reflects the current state of the tables. Add another owner and car:

INSERT INTO people VALUES (41, 'Anna');
INSERT INTO cars VALUES ('Fiat', 'Panda', 'Anna');

Query the view again and the Panda row is already there. No refresh step needed.

That is also the catch. A view does not make a slow query fast, because the query still runs on every access. If you need stored, precomputed results, PostgreSQL offers materialized views, which trade freshness for speed. A plain view is about naming and reuse, not performance.

To change a view’s definition, PostgreSQL and MySQL accept CREATE OR REPLACE VIEW car_age AS .... In SQLite you drop the view and create it again.

The view is persistent, and will look like a table in your database. You can delete a view using DROP VIEW:

DROP VIEW car_age

Dropping a view never touches the underlying tables. Only the saved query is removed. The reverse is not true: if you try to drop a table a view depends on, PostgreSQL refuses with a dependency error until the view is gone.

Related posts about database: