T-SQL Conditional WHERE Clause
You can write it as
SELECT p.*
FROM Locations l
INNER JOIN Posts p
ON l.LocationId = p.LocationId
WHERE l.Condition1 = @Value1
AND l.SomeOtherCondition = @SomeOtherValue
AND ((@IncludeBelow = 1) OR (p.LocationTypeId = @LocationType))
which is a pattern you see a lot e.g. for optional search parameters. But IIRC that can mess up the query execution plans so there may be a better way to do this.
Since it's only a bit, it almost might be worth deciding between two blocks of SQL with or without the check, e.g. using an IF in a stored procedure or with different command strings in calling code, based on the bit?
I changed the query to use EXISTS because if there's more than one location associated with a POST, there'd be duplicate POST records that'd require a DISTINCT or GROUP BY clause to get rid of...
The non-sargable
This will perform the worst of the possible solutions:
SELECT p.*
FROM POSTS p
WHERE EXISTS(SELECT NULL
FROM LOCATIONS l
WHERE l.LocationId = p.LocationId
AND l.Condition1 = @Value1
AND l.SomeOtherCondition = @SomeOtherValue)
AND (@IncludeBelow = 1 OR p.LocationTypeId = @LocationType)
The sargable, non-dynamic version
Self explanitory....
BEGIN
IF @IncludeBelow = 0 THEN
SELECT p.*
FROM POSTS p
WHERE EXISTS(SELECT NULL
FROM LOCATIONS l
WHERE l.LocationId = p.LocationId
AND l.Condition1 = @Value1
AND l.SomeOtherCondition = @SomeOtherValue)
AND p.LocationTypeId = @LocationType
ELSE
SELECT p.*
FROM POSTS p
WHERE EXISTS(SELECT NULL
FROM LOCATIONS l
WHERE l.LocationId = p.LocationId
AND l.Condition1 = @Value1
AND l.SomeOtherCondition = @SomeOtherValue)
END
The sargable, dynamic version (SQL Server 2005+):
Love or hate it, dynamic SQL lets you write the query once. Just be aware that sp_executesql caches the query plan, unlike EXEC in SQL Server. Highly recommend reading The Curse and Blessings of Dynamic SQL before considering dynamic SQL on SQL Server...
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT p.*
FROM POSTS p
WHERE EXISTS(SELECT NULL
FROM LOCATIONS l
WHERE l.LocationId = p.LocationId
AND l.Condition1 = @Value1
AND l.SomeOtherCondition = @SomeOtherValue)'
SET @SQL = @SQL + CASE
WHEN @IncludeBelow = 0 THEN
' AND p.LocationTypeId = @LocationType '
ELSE ''
END
BEGIN
EXEC sp_executesql @SQL,
N'@Value1 INT, @SomeOtherValue VARCHAR(40), @LocationType INT',
@Value1, @SomeOtherValue, @LocationType
END
You can change your CASE
statement to this. The query planner sees this differently, but it may be no more efficient than using OR:
(p.LocationTypeId = CASE @IncludeBelow WHEN 0 THEN p.LocationTypeId ELSE @LocationType END)