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.

Mads Nielsen · Accepted Answer · 2016-03-26 22:36:43Z

4

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

answered Mar 26, 2016 at 22:36

Mads Nielsen

6445 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mads Nielsen Over a year ago

Note, that jhilden's suggestion is more efficient, since you'd save yourself from a join in the situations where it's not needed. I do, however, understand your argument of keeping just one query.

Daniel Over a year ago

Thanks a lot, this seems to be OK, I gotta test it. And yes, you're right, we also have to test if the extra joins cause so much overhead that it's worth duplicating the code.

Daniel Over a year ago

I accepted your answer as we have implemented this solution for testing purposes. It works as needed and gets the job done, but performance should be measured. There are many other answers that fit the bill, so thanks for everyone who took the time to fiddle with my problem.

jhilden · Accepted Answer · 2016-03-26 22:13:51Z

3

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;

answered Mar 26, 2016 at 22:13

jhilden

12.5k5 gold badges56 silver badges77 bronze badges

3 Comments

Daniel Over a year ago

This is what I tried to avoid: duplicating the select. The exact select in our SP is a lot more complicated (like 150 rows) and gets very ugly if the whole shebang is duplicated... not to mention future modifications.

paparazzo Over a year ago

@Daniel If the whole shebang is 150 rows all the more reason to have an efficient query. How hard can a copy paste and remove a join be?

Daniel Over a year ago

You're absolutely right in this matter. We will have to do some stress test on real data to figure the best answer. I was merely looking for an alternative to code duplication, but I never expected to get so many good suggestions (including staying on the current track, just as jhilden - and you - suggested).

Adam Silenko · Accepted Answer · 2016-03-26 22:52:45Z

3

you can use exists condition, like this:

SELECT
 ID,
 DepartmentID,
 FileName
FROM Document
WHERE exists(
 SELECT 1 from AllowedDepartmentList 
 WHERE DepartmentID = AllowedDepartmentList.ID) 
OR @IsAdmin = 'true'

answered Mar 26, 2016 at 22:52

Adam Silenko

3,1062 gold badges19 silver badges33 bronze badges

3 Comments

Adam Silenko Over a year ago

I'm think it is faster, especially if you use additional conditions, but you must try. join is to get data, exist is perfect for you :)

Adam Silenko Over a year ago

Join can cause records multiplication, exists not.

Daniel Over a year ago

Hmmm. Interesting comments. Will definitely check this suggestion.

Pரதீப் · Accepted Answer · 2016-03-27 02:33:18Z

Another way to do this using Dynamic query. This will have good performance when compared to JOIN/Exists options

DECLARE @sql     NVARCHAR(max)='',
        @IsAdmin BIT = 1

SET @sql = '
SELECT
    ID,
    DepartmentID,
    FileName
FROM
    Document D
    ' + CASE WHEN @IsAdmin = 'false' THEN ' where exists (select 1 from  AllowedDepartmentList AD where D.DepartmentID = AD.ID ) ' ELSE '' END

--PRINT @sql 

exec sp_executesql @sql

Dynamically framed query when @IsAdmin = 0

SELECT ID,
       DepartmentID,
       FileName
FROM   Document D
WHERE  EXISTS (SELECT 1
               FROM   AllowedDepartmentList AD
               WHERE  D.DepartmentID = AD.ID)

Dynamically framed query when @IsAdmin = 0

SELECT ID,
       DepartmentID,
       FileName
FROM   Document D

Anish Gopi · Accepted Answer · 2016-03-26 22:43:08Z

2

The following query would help you

SELECT  ID
        ,DepartmentID
        ,FileName
FROM    Document
        LEFT JOIN   AllowedDepartmentList ON DepartmentID = AllowedDepartmentList.ID
WHERE   @isAdmin = 1
    OR  ( @isAdmin = 0 AND AllowedDepartmentList.ID IS NOT NULL)

answered Mar 26, 2016 at 22:43

Anish Gopi

663 bronze badges

Comments

paparazzo · Accepted Answer · 2016-03-27 05:27:03Z

1

add an AllowedDepartmentList.ID of like -1 for admin
pass a null or -1 for @adminID

ON isnull(@adminID, DepartmentID) = AllowedDepartmentList.ID

An OR (or @IsAdmin = 'true' ) is not efficient and this also just returns 1

edited Mar 27, 2016 at 5:27

answered Mar 27, 2016 at 4:46

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

2 Comments

Pரதீப் Over a year ago

This going to kill any index present on DepartmentID

paparazzo Over a year ago

@Prdp So, I assume AllowedDepartmentList.ID is a PK. It is no worse than an EXISTS with less code.

Collectives™ on Stack Overflow

Conditional filtering with join

6 Answers 6

3 Comments

3 Comments

3 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

3 Comments

3 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related