"Small cheer and a great welcome makes a merry feast."
Comedy of Errors, Act 3, scene 1
Topics Covered in This Chapter
- What Is a Set, Anyway?
- Operations on Sets
- SQL Set Operations
By now, you know how to create a set of information by asking for specific columns or expressions on columns (SELECT), how to sort the rows (ORDER BY), and how to restrict the rows returned (WHERE). Up to this point, we've been focusing on basic exercises involving a single table. But what if you want to know something about information contained in multiple tables? What if you want to compare or contrast sets of information from the same or different tables?
Creating a meal by peeling, slicing, and dicing a single pile of potatoes or a single bunch of carrots is easy. From here on out, most of the problems we're going to show you how to solve will involve getting data from multiple tables. We're not only going to show you how to put together a good stew—we're going to teach you how to be a chef!
Before digging into this chapter, you need to know that it's all about the concepts you must understand in order to successfully link two or more sets of information. We're also going to give you a brief overview of some specific syntax defined in the SQL Standard that directly supports the pure definition of these concepts. Be forewarned, however, that many current commercial implementations of SQL do not yet support this "pure" syntax. In later chapters, we'll show you how to implement the concepts you'll learn here using SQL syntax that is commonly supported by most major database systems. What we're after here is not the letter of the law but rather the spirit of the law.