Relational Division via Double Negation

Snippet

Relational Division via Double Negation

This pattern implements universal quantification (the 'FOR ALL' operator) using relational algebra. It identifies entities (students) that are associated with every entry in a target set (courses) by ensuring there is no course that the student has not enrolled in. This is more robust than COUNT-based comparisons as it handles set relationships purely through logic.

snippet.sql
sql
SELECT s.name 
FROM students s 
WHERE NOT EXISTS (
    SELECT c.id 
    FROM courses c 
    WHERE NOT EXISTS (
        SELECT 1 
        FROM enrollment e 
        WHERE e.student_id = s.id 
        AND e.course_id = c.id
    )
);

Breakdown

SELECT s.name FROM students s

Selects the candidate records from the primary entity table.

WHERE NOT EXISTS (SELECT c.id FROM courses c ...)

Filters for entities where there is no record in the requirements set...

WHERE NOT EXISTS (SELECT 1 FROM enrollment e ...)

...for which a matching relationship record does not exist in the junction table.

Previous snippet Next snippet

From your library

Relational Division via Double Negation

Related