title: VIEW on a table in ClickHouse
body: <p>I'm looking into ClickHouse's VIEW, MATERIALIZED VIEW and LIVE VIEW.</p>
  <p>MATERIALIZED VIEW and LIVE VIEW are pretty well described in the <a href="https://clickhouse.tech/docs/en/sql-reference/statements/create/view/" rel="nofollow noreferrer">official docs</a>.</p>
  <p>However, VIEW on a table description is limited.
  From the official docs:</p>
  <pre><code>As an example, assume you’ve created a view:
  
  CREATE VIEW view AS SELECT ...
  and written a query:
  
  SELECT a, b, c FROM view
  </code></pre>
  <p>But it does not specify which table engines are supported for the table we are doing SELECT from. Also, there are no examples on usage.
  Can you please provide a minimum usage example of VIEW and explain which table engines are supported for the initial table?</p>
  <p>Concerning the difference between the VIEW and MATERIALIZED VIEW types I found this answer for Oracle and it looks like it can be applied to ClickHouse as well <a href="https://stackoverflow.com/questions/93539/what-is-the-difference-between-views-and-materialized-views-in-oracle">What is the difference between Views and Materialized Views in Oracle?</a>.</p>
  <p>Edit: Added few essential examples from tests from the link in comments:</p>
  <p>Example 1</p>
  <pre><code>DROP TABLE IF EXISTS union;
  
  create view union as select 1 as test union all select 2;
  
  SELECT * FROM union ORDER BY test;
  
  DETACH TABLE union;
  ATTACH TABLE union;
  
  SELECT * FROM union ORDER BY test;
  
  DROP TABLE union;
  </code></pre>
  <p>Example 2:</p>
  <pre><code>DROP VIEW IF EXISTS test_view;
  DROP TABLE IF EXISTS test_table;
  
  CREATE TABLE test_table
  (
      f1 Int32,
      f2 Int32,
      pk Int32
  )
  ENGINE = MergeTree()
  ORDER BY f1
  PARTITION BY pk;
  
  CREATE VIEW test_view AS
  SELECT f1, f2
  FROM test_table
  WHERE pk = 2;
  
  INSERT INTO test_table (f1, f2, pk) VALUES (1,1,1), (1,1,2), (2,1,1), (2,1,2);
  
  SELECT * FROM test_view ORDER BY f1, f2;
  
  ALTER TABLE test_view DELETE WHERE pk = 2; --{serverError 48}
  
  SELECT * FROM test_view ORDER BY f1, f2;
  
  DROP VIEW IF EXISTS test_view;
  DROP TABLE IF EXISTS test_table;
  </code></pre>