The difference between WITH and CREATE TEMPORARY TABLE

I finished SQL course here and took one more course on Coursera to learn even more. Here, we are taught to use WITH to create temporary tables. On Coursera, they are teaching us to use CREATE TEMPORARY TABLE. What is the difference, if any?

Thanks!

The difference is your ability to reference that temporary table by name in a later query.

If you say WITH purchases AS ... you can reference that the temporary purchases table in only that query.

On the other hand, if you say CREATE TEMP TABLE purchases AS ... you can reference the temporary purchases table in as many queries as you want until you close the connection with that SQLite database (it is automatically deleted upon closing the connection).

So basically, if you think it is a one-off then use WITH. If you want to make several queries from it, use CREATE TEMP TABLE or CREATE TEMPORARY TABLE.

2 Likes