SQL Tutorial: Working with temporary tables

Want to learn more? Take the full course at https://learn.datacamp.com/courses/improving-query-performance-in-postgresql at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work. --- Using temporary tables is another option and one that can speed performance. In this lesson, you will learn what they are and how they work. Temporary, or temp, tables are short-lived storage. They are created similar to any table, using the CREATE TABLE command with the TEMP qualifier. They are appealing because they provide transient storage. They are available for the duration of the database session, meaning they only temporarily tie database resources. Within your database session, they are available in multiple distinct queries. Contrast this with a CTE or subquery which is only available for that one query. Temp tables are user specific, meaning they are available only to you as the creator. Creating a temporary copy of a slow table is a good way to make it faster to query. This example shows a table of holidays by country. It has many entries with each holiday duplicated by country. You can create a TEMP table to look at just holidays in one country, here the USA holidays. Commonly, slow to query tables are the result of a large table, meaning one with many records. As shown here, the prior slide's world holidays table has about half a million rows. The TEMP table of just the USA holidays has only twenty-five. Creating the USA TEMP table will be slow because you create it by querying the large world holidays table. However, it is then stored in memory for the duration of the database session. Queries referencing this USA table will subsequently run faster than those referencing the underlying, world holiday table. Sometimes base table slowness is because the table is actually a view. Views are similar to tables, with one key difference. Tables contain data. The data is materialized and available. Views contain t