Tags:conceptdatabaseSQLSQLDivision Status:🟩

---

SQL Division

Summary

SQL Division is a relational algebra operation that is used to find tuples in one table that are related to all tuples in another table. It is particularly useful when dealing with queries that involve “all” conditions.

Details

SQL Division is used to retrieve rows from one table that match all rows in another table based on a common attribute. It’s commonly used in scenarios where you need to determine entities that meet all criteria specified in a different set.

Examples could be, if you want to find:

• The customers who have purchased all products.
• The students who have taken all courses in a program.
• The airlines who land at all airports in a country.
• The coffeehouses that sell all existing coffees.

Examples

Consider these tables: Customers: CustomerID, CustomerName. Purchases: CustomerID, ProductID.

SQL Division can be used to find customers who have purchased every product:

-- Identify all products
SELECT DISTINCT ProductID FROM Purchases;
 
-- Find customers who have purchased all products
SELECT CustomerID
FROM Purchases
GROUP BY CustomerID
HAVING COUNT(DISTINCT ProductID) = (SELECT COUNT(DISTINCT ProductID) FROM Purchases);