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.

Answer 1

You could use a left join in combination of a where that either requires admin privileges, or a match on the join

SELECT
    ID,
    DepartmentID,
    FileName
FROM
    Document
-- conditional join from here
LEFT JOIN
    AllowedDepartmentList ON DepartmentID = AllowedDepartmentList.ID 
WHERE
    @IsAdmin = 'true' OR AllowedDepartmentList.ID IS NOT NULL

Answer 2

As a general rule, put your parameters into WHERE clauses VS joins. A somewhat simple solution would be to have a TVF or sproc that runs two completely different queries. something like this:

IF (@isAdmin = 0) --notice I used a SQL bool vs a string of 'false'
BEGIN
    SELECT
        ID,
        DepartmentID,
        FileName
    FROM
        Document
    JOIN
        AllowedDepartmentList ON DepartmentID = AllowedDepartmentList.ID;
END;
ELSE
   --@IsAdmin is not false, so don't join
    SELECT
        ID,
        DepartmentID,
        FileName
    FROM
        Document;
END;