Emp. with all Department, Sql Server

Language: Layout:

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

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)

declare @distinctDeptCount int 
SET @DistinctDeptCount = (SELECT Count(Distinct department_id) FROM departments)
--SELECT @DistinctDeptCount

SELECT Distinct employees.employee_id, employee_name
from employees     
where employees.employee_id in (
select employee_id from department_employees GROUP BY employee_id HAVING COUNT(department_id) = @distinctDeptCount
)

View schema
Execution time: 0,05 sec, rows selected: 2, rows affected: 14, absolute service time: 0,23 sec, absolute service time: 0,24 sec