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.