This chapter has been excerpted from "SQL Queries for Mere Mortals" with permission from Addison-Wesley"IntroductionBy 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'regoing 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.What Is a Set, Anyway?If you were a teenager any time from the mid-1960s onward, you might have studied set theory in a mathematics course. (Remember New Math?) If you were introduced to set algebra, you probably wondered why any of it would ever be useful.Now you're trying to learn about relational databases and this quirky language called SQL to build applications, solve problems, or just get answers to your questions. Were you paying attention in algebra class? If so, solving problems- particularly complex ones-in SQL will be much easier.Actually, you've been working with sets from the beginning of this book. In Chapter 1, What Is Relational?, you learned about the basic structure of a relational database-tables containing records that are made up of one or more fields. (Remember that in SQL, records are known as rows, and fields are known as columns.) Each table in your database is a set of information about one subject. In Chapter 2, Ensuring Your Database Structure Is Sound, you learned how to verify that the structure of your database is sound. Each table should contain the set of information related to one and only one subject oraction.In Chapter 4, Creating a Simple Query, you learned how to build a basic SELECT statement in SQL to retrieve a result set of information that contains specific columns from a single table and how to sort those result sets. In Chapter 5, Getting More Than Simple Columns, you learned how to glean a new set of information from a table by writing expressions that operate on one or more columns. In Chapter 6, Filtering Your Data, you learned how to restrict further the set of information you retrieve from your tables by adding a filter (WHERE clause) to your query.As you can see, a set can be as little as the data from one column from one row in one table. Actually, you can construct a request in SQL that returns no rows-an empty set. Sometimes it's useful to discover that something does not exist. A set can also be multiple columns (including columns you create with expressions) from multiple rows fetched from multiple tables. Each row in a result set is a member of the set. The values in the columns are specific attributes of each member-data items that describe the member of the set. In the next several chapters,we'll show how to ask for information from multiple sets of data and link these sets together to get answers to more complex questions. First, however, you need to understand more about sets and the logical ways to combine them.Operations on SetsIn Chapter 1,we discussed how Dr. E. F. Codd invented the relational model on which most modern databases and SQL are based. Two branches of mathematics- set theory and first-order predicate logic-formed the foundation of his new model.After you graduate beyond getting answers from only a single table, you need to learn how to use result sets of information to solve more complex problems. These complex problems usually require using one of the common set operations to link data from two or more tables. Sometimes, you'll need to get two different result sets from the same table and then combine them to get your answer.The three most common set operations are as follows.
In the following three sections,we'll explain these basic set operations-the ones you should have learned in high school algebra. The SQL Set Operations section later in this chapter gives an overview of how these operations are implemented in "pure" SQL.