Dr. Codd’s original relational algebra had eight basic operations. Since relational database management systems are based on set theory, the first four are traditional set operations: intersection, set difference, union, and product. These operations are available in SQL, respectively, as INTERSECT, EXCEPT, UNION, and CROSS JOIN. The next four are row-oriented: restriction, projection, (natural) join, and divide. These operations are available in SQL, respectively, as rows picked with a WHERE or ON clause, the column list in a SELECT list, a simple INNER JOIN..ON operator and, well, we do not have a simple divide in SQL! SQL also has several OUTER JOINs, OUTER UNION, variants of the ON clause, and the multiple-set INTERSECT ALL, EXCEPT ALL and UNION ALL extensions.
But we never added relational division. It can be written with the other operators, and it turns out that it is not so simple after all. The idea of relational division is that a divisor table is used to partition a dividend table and produce a quotient or results table. The quotient table is made up of those values of one column for which a second column had all of the values in the divisor.
Read the 12-page whitepaper “Relational Division” by Joe Celko to learn about the different approaches to implement relational division using SQL code operators. The whitepaper describes relational division operators, division with remainders, exact division, a note on performance, Todd’s division, division with set operators, Romley’s division, and a programming problem.
Click here to download the whitepaper.
The author, Joe Celko, is the author of a series of ten books on SQL and RDBMS (MKP/Elsevier) that have been in print for over 20 years. He served for 10 years on the ANSI/ISO database standards committee. He has written columns and articles for the IT trade press for over 30 years. He currently enjoys being a TEALS volunteer and judging the local High School Science Fest once a year.
