SQL Conditional JOIN

I have three tables 'Employees', 'Departments' and 'EmployeesInDepartments' The 'Employees' tables references the 'Departments' table (Each employee must have a DepartmentId). However, an employee can exist in multiple departments by adding entries (EmployeeId and DepartmentId) to 'EmployeeInDepartments' table.

I currently have the following stored procedure to retrieve employees by departmentId:

CREATE PROCEDURE dbo.CollectEmployeesByDepartmentId
    (
    @DepartmentId int,
    @IsDeleted bit
    )
AS
BEGIN
        SELECT   Employees.*
        FROM      Employees 
        WHERE   ((Employees.IsDeleted = @IsDeleted )
            AND ((Employees.DepartmentId = @DepartmentId)
                OR (Employees.EmployeeId IN (SELECT EmployeesInDepartments.EmployeeId
                                        FROM EmployeesInDepartments 
                                        WHERE (EmployeesInDepartments.DepartmentId = @DepartmentId)
                                        )
开发者_运维知识库                    )
                )
        )   
END

How can I optimize this stored procedure and possibly use JOINS?

My first recommendation to you is to remove department Id from the employee table. Insert all records to the employees in Departments table.

Then it's a simple inner join.

And of course, never use select * in production code.

Here's my re-write of your query:

WITH summary AS (
   SELECT e.*
     FROM EMPLOYEES e
    WHERE e.isdeleted = @IsDeleted 
      AND e.parentid = 0)
SELECT a.*
  FROM summary a
 WHERE a.departmentid = @DepartmentId
UNION
SELECT b.*
  FROM summary b
  JOIN EMPLOYEESINDEPARTMENTS ed ON ed.employeeid = b.employeeid
                                AND ed.departmentid = @DepartmentId

The UNION is necessary to remove duplicates - if you know there'll never be duplicates, change UNION to UNION ALL.

The CTE called "summary" doesn't provide any performance benefit, it's just shorthand.

   SELECT E.*
   FROM Employees E
      Left Join EmployeesInDepartments EID ON E.EmployeeId = EID.EmployeeId
         And E.DepartmentId <> @DepartmentId 

   WHERE E.IsDeleted = @IsDeleted
      And
      (  
         E.DepartmentId = @DepartmentId 
         Or (EID.DepartmentId = @DepartmentId)
      )

Edit to include IsDeleted logic. I do agree with some of the other answers, your design should probably be changed. But this query should do it. If you have duplicates in EmployeesInDepartments, you can change that to a Select Distinct.

I would suggest you change the IN clause used in your query to WHERE EXISTS.

If you are using IN in your query it means you are not taking benifits of the indexes defined on the table, and the query will perform a complete table scan which impacts the query performance.

You can check this thread to convert a IN to WHERE EXISTS:

Changing IN to EXISTS in SQL