Querying Views

Once a view exists, you can query it with normal SELECT syntax.

This lets you reuse a complex base query with simple downstream statements.

Core Concepts

flowchart TD
  A[SELECT from view] --> B[SQLite expands view definition]
  B --> C[Executes against base tables]
  C --> D[Returns final result set]

Query operation	Works on views?
SELECT with WHERE	Yes
SELECT with ORDER BY	Yes
Join view with table/view	Yes

Code Examples

-- Setup a simple base table and view for this lesson.
CREATE TABLE orders_vq (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  total        REAL NOT NULL
);

INSERT INTO orders_vq (order_id, customer_id, total)
VALUES (1, 10, 25.0), (2, 10, 95.0), (3, 20, 60.0);

CREATE VIEW v_orders_vq AS
SELECT order_id, customer_id, total
FROM orders_vq;

Expected output
Table and view created with three rows available through the view.

-- Query view with filter and sorting.
SELECT order_id, customer_id, total
FROM v_orders_vq
WHERE total >= 60
ORDER BY total DESC;

order_id	customer_id	total
2	10	95.0
3	20	60.0

-- Aggregate over view output.
SELECT customer_id, SUM(total) AS total_spent
FROM v_orders_vq
GROUP BY customer_id
ORDER BY customer_id;

customer_id	total_spent
10	120.0
20	60.0

SQLite-Specific Nuances

SQLite Nuance

In SQLite, querying a view is logically equivalent to running its underlying SELECT definition.

Views improve query organization; they are not automatic cache layers.

Common Pitfalls / Best Practices

Pitfall

Assuming view queries are always faster than querying base tables directly.

Views are primarily for abstraction and reuse, not guaranteed performance gains.

Best Practice

Use views to simplify repeated business logic and keep calling queries small and focused.

Quick Challenge

Query v_orders_vq to return only the highest total row.

View Solution

SELECT order_id, customer_id, total
FROM v_orders_vq
ORDER BY total DESC
LIMIT 1;

order_id	customer_id	total
2	10	95.0