Head First SQL, 2nd Edition

Chapter 12. Temporary Tables: Short-Term Storage

Not everything needs to be permanent. So far, you’ve stored your tables of data on your computer’s hard drive, which means you’ve had unlimited access to them, even after signing out of SQL or turning off your machine. But not all tables need to be written in permanent ink. Sometimes you’ll dabble with a table for a little while, but like a bad tattoo, you hope it will just disappear on its own. Temporary tables do exactly that. Temporary tables act just like regular ones, except that they automatically delete themselves when you log out of your SQL session. If you’re worried about results from an expensive query vanishing or your intermediate tables taking up too much disk space, avoid that instant regret and give temporary tables a try.

Hot and bothered

Here is Abby’s first query. Her subquery gets the ID number for “hot” weather. Then her outer query returns the city and country names for places with weather IDs that match the “hot” identifier.

Query #1

SELECT city, country
FROM locations
WHERE weather_id = (SELECT weather_id
                    FROM weather
                    WHERE weather = 'hot');

Note

Abby’s subquery finds the “hot” weather ID number.