Chapter 25. Relational division
Relational division is one of the hardest-to-understand areas in relational theory because of the complexity involved. Yet relational division is one of the areas that can be most useful when programming for relational databases.
There are many situations where relational division is a good solution. A simple way to think about relational division’s usefulness is to consider situations where you need data from one set that matches ALL the values in another set. For example, you may want to find all customers who have been served by both James and Gabrielle. Or you may want to find all applicants who have experience with Reporting Services, SharePoint, and Integration Services. What if you run a dating site and you want your clients to be able to search for certain criteria such as eye color, number of children, and so on? Possible dating choices will only be returned when someone matches ALL of the specified criteria.
A common mistake is to think you can use a series of INNER JOIN(s) to find the solution, but you can’t do that in an efficient way. INNER JOIN will allow you to find rows in one set that match any one of the rows from another set, but in order to find those that match ALL, you’d have to build a temporary table of intermediate results, and then use a loop to check for each value of interest. A relational division algorithm deals with this special problem.