0

So I believe that in SQL if I want to temporary create a new table and use to make some queries, I can use CREATE VIEW which would make a "virtual table" with no physical data but can be used to query and is always updated.

VS

The SQL WITH clause allows you to give a sub-query block a name as well?

If someone could explain the differences to me it would be very appreciated thank you!

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
mathpun
  • 39
  • 6
  • possible duplicate of [Difference between CTE and SubQuery?](http://stackoverflow.com/questions/706972/difference-between-cte-and-subquery) – cha Feb 27 '14 at 23:29
  • 1
    Something you can do with `WITH` that you can't do with the other objects (views, tables) is perform recursive functions in one statement. i.e. if you have a table that recursively references itself like an employee org structure or something you can write one recursive CTE that walks all the way down the chain. http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx @Erwin already made this point but I think it's worth highlighting. – Nick.Mc Feb 27 '14 at 23:33
  • It might also help if you told us which DBMS you are using –  Feb 27 '14 at 23:47

2 Answers2

1

If you want to create a temporary table, just create a temporary table and drop it when you are done with it

CREATE TABLE #MyTable (Id INT) -- Add whatever other columns you need
-- Populate the table as needed
-- Run some queries
DROP TABLE @MyTable

Views are for denormalizing data to be convenient for queries (in general, not in a temporary manner). The SQL WITH clause is for using a subquery as part of a larger query, and the subquery is not available outside of the larger query. The main difference between them is the scope of availability (view persists vs the subquery is temporary), and presence of additional features with views (such as security, constraints, etc.)

mayabelle
  • 9,804
  • 9
  • 36
  • 59
1

A view is a persisted SELECT query that can be called by any user with access and privileges for the underlying tables. Just the query is persisted (the recipe), not the result. You can SELECT from it, just like you would SELECT from a table. Depending on your RDBMS you can store additional attributes and regulate access privileges.
In some RDBMS, even write operations on views are implemented, which are forwarded to the underlying tables.

A CTE (Common Table Expression, a.k.a. "WITH clause") is similar to a view, but only visible to the query it is attached to and not persisted. There are differences in the implementations of various RDBMS.
Unlike a subquery, a CTE can be reused on multiple query levels independently.

A temporary table is something completely different. It's an actual table with it's own data, just not persisted and lost at the end of the session (or sometimes at the end of the transaction).

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • So a VIEW is a sub-query and a WITH is a CTE? And that means that a VIEW can easily be re-used where a CTE is more of a one-time use deal? – mathpun Feb 27 '14 at 23:45
  • @user3290443: Yes, except that a [subquery](http://en.wikipedia.org/wiki/Subquery#Subqueries) is *not* a view at all. Typically it's a nested `SELECT` statement, part of a bigger query. I added some links to Wikipedia definitions, since your actual RDBMS is unknown. – Erwin Brandstetter Feb 27 '14 at 23:55