SQL Views Explained: CREATE VIEW with Examples

# Save a query: CREATE VIEW You need one when the same long query is used again and again: write it once, give it a name, and everyone queries the name. Appian record types can use a view as their source. A view is a saved SELECT with a name. You query it like a table. - `CREATE VIEW order_details AS` save the SELECT that follows under this name - `SELECT * FROM order_details` use it like a table > Name a view after what it returns: order_details, active_customers. Some teams start view names with v_ so they're easy to tell from tables.

CREATE VIEW order_details AS
  SELECT o.id,
    c.name AS customer,
    p.name AS pizza, o.qty
  FROM orders o
  JOIN customers c
    ON c.id = o.customer_id
  JOIN pizzas p
    ON p.id = o.pizza_id;
SELECT * FROM order_details

# Every column needs its own name c.name and p.name are both called name. A plain SELECT allows that; a view doesn't. MySQL says "Duplicate column name". - `AS customer` `AS pizza` give the two columns different names > When a view won't create, check the column names first. Two tables' id or name columns are the usual clash.

CREATE VIEW order_names AS
  SELECT o.id, c.name, p.name
  FROM orders o
  JOIN customers c
    ON c.id = o.customer_id
  JOIN pizzas p
    ON p.id = o.pizza_id;
SELECT * FROM order_names

# A view stores the question, not the answer The view runs its SELECT every time you use it. Change the table, and the view shows the change straight away. > A view never goes out of date, because it keeps no copy of the data.

CREATE VIEW order_details AS
  SELECT o.id,
    c.name AS customer, o.qty
  FROM orders o
  JOIN customers c
    ON c.id = o.customer_id;
UPDATE orders
SET qty = 10
WHERE id = 1;
SELECT *
FROM order_details
WHERE id = 1

# Views don't make queries faster Every use of a view runs its whole saved SELECT again, joins and all. A slow query saved as a view is still slow. > Some databases have materialized views that store the answer. MySQL and MariaDB don't. For speed, use indexes: that's the next level.

# Query a view like a table - `WHERE city = 'London'` filter it - `ORDER BY` sort it, and so on > For simple views, the database merges your WHERE into the view's own SELECT, so filtering a view is as fast as filtering the original query.

CREATE VIEW order_details AS
  SELECT o.id,
    c.name AS customer, c.city,
    o.qty
  FROM orders o
  JOIN customers c
    ON c.id = o.customer_id;
SELECT *
FROM order_details
WHERE city = 'London'

# Views can't take inputs There is no "order_details for customer 4". Put the column in the view, and add the WHERE from outside, every time. > When you really need an input, use a stored procedure, two levels on. In Appian, a record type on a view does the filtering for you.

CREATE VIEW order_details AS
  SELECT o.id,
    c.id AS customer_id,
    c.name AS customer, o.qty
  FROM orders o
  JOIN customers c
    ON c.id = o.customer_id;
SELECT *
FROM order_details
WHERE customer_id = 4

# Why use a view? - Write a hard query once, and everyone uses the same one - Show only some columns: this one has names and cities, but no emails - Appian can use a view as the source of a record type > A view that leaves out columns is a simple way to share data safely: give a report tool access to the view, not the table.

CREATE VIEW customer_directory AS
  SELECT name, city FROM customers;
SELECT * FROM customer_directory

# Change or remove a view - `CREATE OR REPLACE VIEW` change a view's SELECT - `DROP VIEW` delete the view; the tables underneath are untouched > Before you change a view, check what uses it. Appian record types, reports and other views all depend on its columns.

CREATE VIEW cheap_pizzas AS
  SELECT name, price
  FROM pizzas
  WHERE price < 10;
CREATE OR REPLACE VIEW cheap_pizzas
  AS
  SELECT name, price
  FROM pizzas
  WHERE price < 11;
SELECT * FROM cheap_pizzas