Whitepaper : Relational Division
Author: Joe Celko
Share This:
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.
This whitepaper covers 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.
Presenter: Joe Celko
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.
Register to read the full whitepaper.
Topics :
Database Development,SQL Query Performance,
Products :
SQL Query Tuner,
Author: Joe Celko
Share This:
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.
This whitepaper covers 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.
Presenter: Joe Celko
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.
Register to read the full whitepaper.
Topics : Database Development,SQL Query Performance,
Products : SQL Query Tuner,