Difference between SQL View and WITH clause

sql sql-view
The87Boy picture The87Boy · May 20, 2012 · Viewed 9.9k times · Source

Can anybody in here tell me the difference of VIEW and WITH, as I have searched many placed and I can't find anything about it.
My thoughts is that VIEW and WITH are the same, except for VIEWs is saved as a Schema-object, but I can be wrong

Answer

Gordon Linoff picture Gordon Linoff · May 20, 2012

SQL views and with clauses are very similar. Here are some differences.

Views create an actual object in the database, with associated metadata and security capabilities. With statements are only part if a single query.

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

With statements offer the opportunity to have recursive CTEs, in some databases. This is not possible for views.

For simple subqueries incorporated into queries, they are quite similar. The choice really depends on whether you want to create a reusable code (views) or are focused on a single query (with).

Related questions

Data from two tables into one view

Is it possible to grab data from two tables (that have the same fields) into one view. Basically, so the view sees the data as if it was one table.

Read More
Select Columns of a View

I'm attempting to select the column names of a view in a similar way as selecting from information_schema.columns. I can't seem to find a way to do this. Has anyone else done this before or know if it …

Read More
Error: Could not use view or function because of binding errors

I got read only access to Views and when i am trying to query the View i got this error message. Can anyone help me understand what is the actual problem and how to fix it. FYI.. this is the 1…

Read More