Relational Division via Double Negation

Snippet

Relational Division via Double Negation

Relational division is used to identify entities that are associated with every record in a target set. Since SQL lacks a native 'DIVIDE' operator, we use double negation with NOT EXISTS to find projects where there is 'no skill that is not present in this project'.

snippet.sql
sql
SELECT DISTINCT p.id
FROM Projects p
WHERE NOT EXISTS (
    SELECT s.id
    FROM Skills s
    WHERE NOT EXISTS (
        SELECT 1
        FROM Project_Skills ps
        WHERE ps.project_id = p.id
        AND ps.skill_id = s.id
    )
);

Breakdown

WHERE NOT EXISTS (SELECT s.id FROM Skills s ...)

Starts the check for any skill that might be missing from the project.

WHERE NOT EXISTS (SELECT 1 FROM Project_Skills ps ...)

The inner negation checks if the specific project-skill relationship is absent.

Previous snippet Next snippet

More Expert SQL exercises →

From your library

Relational Division via Double Negation

Related