TABLE REVISITED

A table is the most fundamental concept in a relational database. CREATE TABLE was introduced in Chapter 1 and used in every chapter ever since, yet it still holds some tricks up its sleeve.

When you create a table, its permanence is assumed; after all, we are going to store data, right? When you need to store data permanently, you create a table; when you need to store temporary data, you create a temporary table.

If your data processing is too complex to be accomplished in one single sweep, you might want to consider a place to store intermediate results to be used further down the line. Once the processes using this intermediate data are completed, the temporary storage area, along with the intermediate data, simply goes away. This is the idea behind the temporary table.

SQL syntax for creating temporary tables varies across the RDBMSs, and some, like Microsoft Access, while supporting the idea in principle, do not include it as part of SQL implementation.

There are lots of gotchas and important details you need to master to count this object type confidently as part of your SQL toolbox, yet some basics will help you to get started.

A temporary table normally has a scope, it can be global or local, and its behavior depends on the RDBMS and context. As you might expect, there is some confusion in terms. For example, Oracle defines all temporary tables as GLOBAL, but they can only be transaction-specific or session-specific (see Chapter 10 for more information). ...

Get Discovering SQL: A Hands-On Guide for Beginners now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.