This chapter is from the book
This chapter is from the book
This chapter is from the book
Operations on Sets
In 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.
- Intersection—You use this to find the common elements in two or more different sets: "Show me the recipes that contain both lamb and rice." "Show me the customers who ordered both bicycles and helmets."
- Difference—You use this to find items that are in one set but not another: "Show me the recipes that contain lamb but do not contain rice." "Show me the customers who ordered a bicycle but not a helmet."
- Union—You use this to combine two or more similar sets: "Show me all the recipes that contain either lamb or rice." "Show me the customers who ordered either a bicycle or a helmet."
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.