Difference between CTE and View - SQL

             Difference between CTE and View

               A CTE is a temporary/logical View, it is not store physically. It is a named query, the result for which is only available to the very next query after the CTE is defined. CTE is defined using WITH clause.

                  A View is a physical object that is present in the database. View is as good as a Table but it doesn't store data physically as compared to a table, only the data schema is stored in View. View,when referred, pulls data by executing the query that is associated with it.

                 The biggest difference between a CTE and View, is that, View or derived table cannot call itself, whereas CTE can call itself and hence support recursion.

                In many databases, views have options, for instance to index them.

Also See:  Having vs Where clause ,  Difference between temp table and CTE , CTE(Common Table Expressions) in SQL  , Difference between DELETE and TRUNCATE command