sql server - What would be the query for Employee who work for all the department?

Question

What would be the query for the employee who worked for all the department. here department and employee have many to many cardinality.

The tables are:

CREATE TABLE employees
(
    employee_id int NOT NULL CONSTRAINT pk_employees PRIMARY KEY,
    employee_name nvarchar(128) NOT NULL CONSTRAINT uk_employees_employee_name UNIQUE
);

CREATE TABLE departments
(
    department_id int NOT NULL PRIMARY KEY,
    department_name nvarchar(128) NOT NULL CONSTRAINT uk_departments_department_name UNIQUE
);

CREATE TABLE department_employees
(
    department_id int NOT NULL CONSTRAINT fk_department_employees_departments REFERENCES departments(department_id),
    employee_id int NOT NULL CONSTRAINT fk_departement_employees_employees REFERENCES employees(employee_id),
    CONSTRAINT pk_deparment_employees PRIMARY KEY (department_id, employee_id)
)

Sample data:

INSERT INTO employees
VALUES (1, 'John Doe'), (2, 'Jane Doe'), (3, 'William Doe'), (4, 'Margaret Doe')

INSERT INTO departments
VALUES (1, 'Accounting'), (2, 'Humman Resources'), (3, 'Marketing')

INSERT INTO department_employees
VALUES 
    (1, 1), (2, 1), (3, 1),
    (2, 2), (2, 3),
    (3, 3), (3, 4)

Expected results:

+-------------+---------------+
| employee_id | employee_name |
+-------------+---------------+
|           1 | John Doe      |
+-------------+---------------+

Answer 1

This operation is called Relation Division: on Relational algebra.

It can be implemented in sql with a query like the following

SELECT *
FROM dbo.employees e
WHERE 
    NOT EXISTS (
        SELECT *
        FROM departments d
        WHERE d.department_id NOT IN (
            SELECT dp.department_id
            FROM department_employees dp
            WHERE dp.department_id = d.department_id AND dp.employee_id = e.employee_id
        )
    )

Notice that the query: "Give me the employees that work for all departments" is equivalent to "Give me all employees that there is no department that the employee is not working for"