Displaying Department Name Having Most Number of Employees in SQL Server

There are multiple methods to display the department name having the most number of employees in SQL Server. Here are some of them:

Method 1: Using a subquery and the MAX function

SELECT department_name
FROM departments
WHERE department_id = (
  SELECT department_id
  FROM employees
  GROUP BY department_id
  ORDER BY COUNT(*) DESC
  LIMIT 1
);

Explanation: - The subquery SELECT department_id FROM employees GROUP BY department_id ORDER BY COUNT(*) DESC LIMIT 1 returns the department ID with the highest number of employees. - The outer query selects the department name corresponding to that department ID from the departments table.

Method 2: Using a subquery and the TOP or FETCH FIRST syntax

SELECT department_name
FROM departments
WHERE department_id = (
  SELECT TOP 1 department_id
  FROM employees
  GROUP BY department_id
  ORDER BY COUNT(*) DESC
);

-- or

SELECT department_name
FROM departments
WHERE department_id = (
  SELECT department_id
  FROM employees
  GROUP BY department_id
  ORDER BY COUNT(*) DESC
  FETCH FIRST 1 ROW ONLY
);

Explanation: - The subquery SELECT TOP 1 department_id FROM employees GROUP BY department_id ORDER BY COUNT(*) DESC or SELECT department_id FROM employees GROUP BY department_id ORDER BY COUNT(*) DESC FETCH FIRST 1 ROW ONLY returns the department ID with the highest number of employees. - The outer query selects the department name corresponding to that department ID from the departments table.

Method 3: Using a JOIN and the MAX function

SELECT d.department_name
FROM departments d
JOIN (
  SELECT department_id, COUNT(*) AS num_employees
  FROM employees
  GROUP BY department_id
) e ON d.department_id = e.department_id
WHERE e.num_employees = (
  SELECT MAX(num_employees)
  FROM (
    SELECT COUNT(*) AS num_employees
    FROM employees
    GROUP BY department_id
  ) x
);

Explanation: - The subquery (SELECT COUNT(*) AS num_employees FROM employees GROUP BY department_id) returns the number of employees for each department. - The subquery (SELECT MAX(num_employees) FROM (SELECT COUNT(*) AS num_employees FROM employees GROUP BY department_id) x) returns the highest number of employees among all departments. - The main query joins the departments table with the subquery that counts the number of employees per department, and filters the result to only include the department(s) with the highest number of employees.