SQL | DIVISION (original) (raw)

Last Updated : 3 Jul, 2024

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:

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(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:

**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

Supply Schema

Here,

**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