Relational Division

[Peter Larsson]

One of join types in Relational Algebra is Division. This implementation is not present in SQL Server at all (and not any other RDBMS what I know of).

A typical solution requiring Relational Division is for example to get all orders that have Item A and Item B in it. Or which customers have been served by both Mr Derrick and Mr Smith.

As a long time consultant and Microsoft SQL Server MVP, I have seen numerous custom implemenations of the Relational Division, with various results in terms of accuracy and performance.
There is no doubt there is a real world need for this operator. Customers today spend a lot of time and money to write their own implementations of their DIVIDE BY clone.

If a DIVIDE BY operator is accessible in SQL Server that would give developers a great tool to do parts of their work and satisfy their customers and/or employers.

As a comparison, this is the algorithm I use today. It is really fast on exact division and very fast on remainder division. This example is built on the sample data made popular by Mr Celko, and some extensions which I have built.

SELECT pa.PilotID,
wa.LevelID
FROM (
SELECT PilotID,
COUNT(*) AS SkillCount
FROM dbo.PilotSkills
GROUP BY PilotID
) AS pa
INNER JOIN (
SELECT LevelID,
COUNT(*) AS LevelCount
FROM dbo.Hangar
GROUP BY LevelID
) AS wa ON wa.LevelCount <= pa.SkillCount -- Change "<=" to "=" for exact division
INNER JOIN dbo.PilotSkills AS p ON p.PilotID = pa.PilotID
INNER JOIN dbo.Hangar AS w ON w.PlaneType = p.PlaneType
AND w.PLaneModel = p.PlaneModel
WHERE w.LevelID = wa.LevelID
GROUP BY pa.PilotID,
wa.LevelID
HAVING COUNT(*) = MIN(wa.LevelCount)

If Relational Division had a standardized command, developers would have a much easier time!
A suggested syntax could be

-- Relational division
DIVIDE dbo.PilotSkills AS t
USING (
SELECT { LevelID, } -- If multiple divisor sets are used
PlaneType
{ , PlaneModel } -- For multiple column division
FROM dbo.Hangar
) AS n ON b.PlaneType = t.PlaneType
{ AND b.PlaneModel = t.PlaneModel } -- For multiple column division
{ BY n.LevelID } -- If there are multiple divisor sets
OUTPUT t.PilotName -- Output, always distinct output
, n.LevelID
INTO dbo.TargetTable
(
PilotName,
LevelID
)
{ OPTION (EXACT) } -- For exact division or default remainder division

//Peso