SQL Queries for Mere Mortals: Thinking in Sets
- What Is a Set, Anyway?
- Operations on Sets
- Intersection
- Difference
- Union
- SQL Set Operations
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
- “Small cheer and a great welcome makes a merry feast.”
- —William Shakespeare
- Comedy of Errors, Act 3, scene 1
Topics Covered in This Chapter
What Is a Set, Anyway?
Operations on Sets
Intersection
Difference
Union
SQL Set Operations
Summary
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.
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 or action.
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.