What would be the difference between WITH clause & temporary table?

Question

In layman terms, what would be the key differences between WITH clause & temporary table?

In which scenario it is better to use one over the other?

Answer 1

WITH clause is used in a select query generally when you have to perform some join on a couple of subqueries which contains complex clauses such as HAVING (though not necessarily). Generally speaking WITH clause can only be used in a SELECT statement.

But in a case where data has to be manipulated, meaning you want to change data on some condition or even would like to delete some rows again depending on some complex condition you would rather like to go with TEMP TABLE. Although most cases would be achieved by WITH it generally comes at a cost of some complex logic, in TEMP table you can have a couple of different SQL statements to achieve the same.

Also, TEMP table is generally used as a staging table rather than a view for SELECT query i.e. TEMP table are used when you want to load tons of data from S3, you might want to load the data into a temp table, analyze the data, remove redundancy and finally merge it into the original table in one go.

While TEMP table is transient only for the current session, WITH is always re-evaluated.

Answer 2

The main difference is that the temporary table is a stored table. A CTE is more akin to a view, and helps you express your SQL in an easier to read, more logical way. The same differences apply between tables and views in that a table gives you the potential to do things in a performant way.

If you were building a very complex query or one that needs to be built in stages, WITH clause/CTE would help you do that. However if you were looking to store data in a table to improve performance, a temporary table would be your best bet. Similarly, a temporary table can be used again and again, so if you had the same code used in multiple queries, you might consider a temporary table rather than a CTE.