In SQL, what is the relation between the "with" clause and views?

I'm reading about views in SQL, and I'm confused about the interplay between开发者_C百科 views(I know they're used for security reasons), and the with clause(used to define "a temporary view whose definition is available only to the query in which the with clause occurs" [Database System Concepts 5th ed.]) ) ).

WITH max_balance(value) AS
SELECT MAX(balance)
FROM account
SELECT account_number
FROM account, max_balance
WHERE account.balance = max_balance.value

---

Views are actually used for several reasons. The two most common are:

• Security, to allow users to see data without seeing the underlying tables
• Simplification, to store a SELECT query as an object and be able to treat the result set as a table

From your question I'm assuming you are referring to CTEs which are normally written as:

;WITH CTE AS
(
  SELECT Stuff...
  FROM Table
  WHERE things

)

A CTE is essentially a disposable view - it only persists until the NEXT QUERY. After the next query, it no longer can be referenced.

CTEs are primarily used for simplification of complicated data sets and for recursion.