SQL | DIVISION

Division in SQL is typically required when you want to find out entities that are interacting with all entities of a set of different types of entities. The division operator is used when we have to evaluate queries that contain the keyword 'all'.

When to Use the Division Operator

You typically require the division operator in scenarios where you need to ascertain entities that meet all specified conditions across different datasets. Here are a few common examples:

• Identifying people who have accounts in every bank within a particular city.
• Determining students who have enrolled in all necessary courses to qualify for graduation.

These use cases involve checking against all elements of a subset, thereby necessitating a division-like operation.

In all these queries, the description after the keyword 'all' defines a set which contains some elements and the final result contains those units who satisfy these requirements.

SQL DIVISION Implementation Techniques

Given two relations(tables): R(x,y), S(y).

• R and S: tables
• x and y: column of R
• y: column of S

R(x,y) div S(y) means gives all distinct values of x from R that are associated with all values of y in S.

Computation of Division: R(x,y) div S(y)

Method 1: Using Cross Join and EXCEPT

Steps:

• Step 1: Find out all possible combinations of S(y) with R(x) by computing R(x) x(cross join) S(y), say r1
• Step 2: Subtract actual R(x,y) from r1, say r2
• Step 3: x in r2 are those that are not associated with every value in S(y); therefore R(x)-r2(x) gives us x that are associated with all values in S.

Query:

SELECT * FROM R 
WHERE x NOT IN ( 
 SELECT x FROM (
(SELECT x , y FROM (SELECT y FROM S ) AS p CROSS JOIN (SELECT DISTINCT x FROM R) AS sp)
 EXCEPT
(SELECT x , y FROM R) 
 ) 
 AS r
); 

Method 2: Using Correlated Subquery and NOT EXISTS

SELECT * FROM R AS sx
WHERE NOT EXISTS (
(SELECT p.y FROM S AS p )
EXCEPT
(SELECT sp.y FROM  R AS sp WHERE sp.x = sx.x ) 
);

Relational Algebra for SQL Division

In relational algebra, division can be achieved through a series of steps involving projection, Cartesian product, and set difference operations. Here’s how it can be structured:

1. Generate All Combinations: Compute the Cartesian product of all possible y values in S with distinct x values in R.

r1 ← πx(R) x S

2. Identify Incomplete Combinations: Subtract the actual dataset R from these combinations to find x values not associated with every y.

r2x ← πx(r1-R)

and,

3. Derive Result: Subtract the identified x values from all x values to get those associated with every y.

result ← πx(R)-r2x
R div S = πx(R)- πx((πx(R) x S) – R) 

SQL DIVISION Examples in Practical Schema

Let us look at some of the examples of SQL DIVISION.

Supply Schema

Here,

• sid means supplierID and pid means partsID.
• Tables: suppliers(sid,pid) , parts(pid).

Example 1. Finding suppliers that supply all parts.

Implementation 1: Using Cross Join and EXCEPT method we get,

Query:

SELECT * FROM suppliers
WHERE sid NOT IN ( 
SELECT sid FROM ( 
 (SELECT sid, pid FROM (SELECT pid FROM parts) AS p CROSS JOIN (SELECT distinct sid FROM supplies) AS sp)
EXCEPT
(SELECT sid, pid FROM supplies)) 
 AS r
); 

Implementation 2: Using Correlated Subquery and NOT EXISTS we get,

Query:

SELECT * FROM suppliers AS s
WHERE NOT EXISTS (
 ( SELECT p.pid FROM parts AS p )
EXCEPT
 (SELECT sp.pid FROM supplies sp WHERE sp.sid = s.sid ) 
);

Company schema

Example 2. List employees who work on all projects controlled by dno=4.

Implementation 1: Using Cross Join and EXCEPT method we get,

SELECT * FROM employee AS e
WHERE ssn NOT IN (
SELECT essn FROM (
 (SELECT essn, pno FROM (SELECT pno FROM project where dno=4) AS p CROSS JOIN (SELECT distinct essn FROM works_on) AS w)
EXCEPT (SELECT essn, pno FROM works_on)
 ) 
 AS r
); 

Implementation 2: Using Correlated Subquery and NOT EXISTS we get,

SELECT * FROM employee AS e
WHERE NOT EXISTS (
 (SELECT pno FROM project WHERE dno = 4)
EXCEPT
 (SELECT pno FROM works_on WHERE essn = e.ssn) 
);

Some more Examples:

1. List supplier who supply all ‘Red’ Parts.(supply schema)
2. Retrieve the names of employees, who work on all the projects that ‘John Smith’ works (company schema)

Important Points about SQL DIVISION

• Division is not supported by SQL implementations. However, it can be represented using other operations.(like cross join, Except, In )
• For division correlated query seems simpler to write but may expensive to execute.
• When implementing division-like operations in SQL, especially using methods involving multiple joins or subqueries, scalability can become a significant issue.