Fooling around with Relational Division is fun. Many TSQL programmers see it for the first time and think of it as either a difficult concept to grasp or something that cannot be used. Neither is the truth. It's actually a very simple concept, especially if you use the simpler join method rather than the double or triple-nested subquery techniques often found on the net. Essentially, the concept answers questions such as "Hey, give me the customers who bought all of the products this month", or "give me the restaurant inspection results for those which passed all of the tests". Most examples attempt to illustrate this concept via some klugey example that is too far fetched, such as this favorite that comes to mind: which pilots fly can fly all types of airplanes. Brilliant. For one thing, most of us don't work for the airlines and would never be confronted with this type of query, and another, 99.9% of pilots can't fly all of the planes anyway. So, here's my stab at showing you this technique, and a variation of the classic example for consideration.
1) Set up the schema
2) Insert data for products and orders
Study this data. The primary key on the Orders table is OrderId, CustId, and ProdId. I could have just as easily modified this into a many-to-many relationship, and had a separate Order and OrderDetail to go along with the Products table, but let's keep this one simple since we're not doing relational modeling in this particular blog posting, m-kay?
3) The Relational Division query
Ok, my turn to try and explain this, so here's what we have. If you remember from beginning mathematics way back, a dividend is the number (or set in our case) that gets divided, the divisor divides the dividend, and the quotient is result of the division. (Relations are being divided, hence "Relational Division"...ugh). So the entire set of data that we're dividing is the set of customer orders by the products available, and the divisor is the answer. Relational Division attempts to find those orders which have all of the products. The GROUP BY in the query assures me that I have the distinct count of items that were purchased in the order, and this count is compared to the count of products in the product table. If it matches, then the customer purchased all of the products. Simple? You bet.
Answer....
We see that in fact customers two and three both ordered all products in the products table. If you had an FK relationship on the table as we do, you would not have to include the WHERE o.ProdID IN (SELECT... ) portion of the statement; removing this would mean that the customer had a product on an order that was not in the product table.
A blog that I found here points out that the technique is many times useless because of the type of query. Not so. In his example, he states that the department table and employee table cannot be used in relational division since employees can't be in more than one department at a time. This conclusion is a result of his database schema. In most circumstances there would be a DepartmentHistory table, and this would be the dividend to make relational division work in his example. Again, we're not modeling data here, but if we're going to mention a flaw in the method, we must first remove flaws in our data model, and his had one in my opinion.
4) Practical version
Here's an expanded version also for your consideration. The thought that we divide the number of products into the data to fetch those who have all products that exists makes sense. It can also be said that you can flip the dividend and divisor, dividing the products by the orders table so to speak. Those with 100% matching are returned in the query, while the others are not. Suppose, however, that you might want those customers who did not purchase all of the products? What if you gave a rebate to every order, and that rebate amount was dependent on the percentage of products ordered. Relational Division can handle this as well, and allow you to select those who only purchase two out of three, or one out of three products on their orders. Here is an example:
Answer...
Conclusion
Relational Division is fun, and a neat technique to keep in mind when crafting TSQL solutions. You probably won't be able to use it every day, but does that make it any less worthwhile? No way! A good DBA, TSQL developer, or programmer will embrace all of the rich tools and methods available, and expand his or her toolbox often and whenever possible, and Relational Division is definitely something to learn and add to yours.
Peace out!
Lee
---------------------------------------------
"Let's see... altitude: 21,000 feet. Speed: 520 knots. Level flight. Course: zero-niner-zero. Trim and mixture: wash, soak, rinse, spin"
>>> Get code here... <<<