Head First SQL

Chapter 9. Subqueries: Queries within queries

Yes, Jack, I’d like a two-part question, please. Joins are great, but sometimes you need to ask your database more than one question. Or take the result of one query and use it as the input to another query. That’s where subqueries come in. They’ll help you avoid duplicate data, make your queries more dynamic, and even get you in to all those high-end concert afterparties. (Well, not really, but two out of three ain’t bad!)

Greg gets into the job recruiting business

So far, the gregs_list database has literally been a labor of love. It’s helped Greg find dates for his friends, but he’s made no money from it.

It occurs to him that he could start a recruiting business where he matches his contacts up with possible jobs.

Greg knows he’s going to need to add new tables for his contacts that are interested in the service. He decides to make them separate one-to-one tables rather than putting that information into my_contacts for two reasons.

First, not everyone in his my_contacts list is interested in the service. This way, he keeps NULL values out of my_contacts.

Second, he might hire people to help him with his business someday and the salary information might be considered sensitive. He may only want to give access to those tables ...