CODEWITHSUNDEEP
sqlsql-servert-sql
fahadash
fahadash

Reputation: 3281

Whats the better way to write this query?

I have the following query, I am trying to find out how many employees have a department ID assigned and how many of them have no department assigned (DepartmentID is not a Foreign Key)

WITH 
archive AS
(SELECT  CASE  COALESCE(tbl_Department.DepartmentID, -33) WHEN -33 THEN 'Department Not Found' ELSE 'Department Found' END  AS DepartmentStatus

FROM dbo.tbl_Employee  
WITH (NOLOCK)

LEFT OUTER JOIN dbo.tbl_Department
 WITH (NOLOCK)
 ON tbl_Employee.DepartmentID = tbl_Department.DepartmentID 
) 

SELECT DepartmentStatus, COUNT(DepartmentStatus) 
FROM archive


GROUP BY DepartmentStatus

The above query works good but it takes too long to execute. I have about a couple hundred thousand employee records and about 4000 department records.

Upvotes: 0

Views: 71

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

I think it could be rewritten as:

SELECT CASE WHEN d.DepartmentID IS NULL THEN 'Not Found' ELSE 'Found' END [Status],
       COUNT(*) [Count]
FROM dbo.tbl_Employee e
LEFT JOIN dbo.tbl_Department d ON e.DepartmentID = d.DepartmentID
GROUP BY CASE WHEN d.DepartmentID IS NULL THEN 'Not Found' ELSE 'Found' END

Upvotes: 0

Metaphor
Metaphor

Reputation: 6405

SELECT SUM(CASE WHEN DepartmentID IS NULL THEN 1 ELSE 0 END) AS EmployeesWithNoDepartment
FROM tbl_Department

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460138

You could start with avoiding the COALESCE function if it's not needed. I assume that -33 is just a placeholder for NULL. Following is clearer and also more efficient since the optimizer can use indexes.

I would write it in this way by using EXISTS and no join at all:

WITH archive AS
(
    SELECT CASE WHEN EXISTS(SELECT 1 FROM dbo.tbl_Department d
                            WHERE d.DepartmentID = e.DepartmentID)
           THEN 'Department Found' 
           ELSE 'Department Not Found' END AS DepartmentStatus
    FROM   dbo.tbl_Employee WITH (NOLOCK) e
)
SELECT DepartmentStatus, Count(*) As Cnt 
FROM archive
GROUP BY DepartmentStatus

Upvotes: 2

Related Questions