Chapter 25. Relational division

 

Peter Larsson

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.

Why use relational division?

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.

Defining relational division

 
 

Background

 
 
 

Sample data for two simple cases

 
 
 
 

Comparison charts

 
 

Let’s go on with the real stuff

 
 

Set-based solution to common relational division

 
 
 

Does one query exist for all types of relational division?

 
 
 
 

Summary

 
 
 

About the author

 
 
sitemap

Unable to load book!

The book could not be loaded.

(try again in a couple of minutes)

manning.com homepage
test yourself with a liveTest