Conditional filtering with join

Question

I have a query similar in nature to the following in a stored procedure that has a single parameter:

SELECT
    ID,
    DepartmentID,
    FileName
FROM
    Document
-- conditional join from here
JOIN
    AllowedDepartmentList ON DepartmentID = AllowedDepartmentList.ID 
                          AND @IsAdmin = 'false'

The parameter is @IsAdmin with the data type bit.

The two tables I work with is the Document table (see structure in query above), and the AllowedDepartmentList that contains a single int column.

I use this query to filter the returned results of the Document table with join. I do not use the WHERE DepartmentID IN() clause, because the AllowedDepartmentList can be as long as 600-700 items (too much for IN() to handle with good performance in a potentially 1M record table) So I filter using a join, but the filtering should only execute, if the @IsAdmin parameter is false. Like the lines after the -- conditional join from here comment weren't even there.

I tried the query above but it produces no records. I suspect I'm using the wrong type of join, but I'm stuck.

Conditional filtering with join

6 Answers