I am using sql server 2017.
I am trying to use the in operator, but it behaves me so differently.
What I am trying to do is
I created a cte and split the comma separated string into array and use this cte in my end query through in operator.
But In operator should return all those results even if any of the value exists within the in operator.
But unfortunately, It is not returning the values Yet If all values exists then It return the result.
See my query please
declare @franchiseId int = 16367;
declare @vehicleCategoryId int = '1';
declare @vehicleCategoryIds nvarchar(100) = '1,8';
-- Select * FROM Franchise Where name like '%catford%'
SET NOCOUNT ON;
DECLARE @p1 Int = 1;
DECLARE @p2 Int = 1;
DECLARE @p3 Int = 1;
DECLARE @p4 Int = 1;
DECLARE @p5 Decimal(5,4) = 0;
DECLARE @p6 Decimal(5,4) = 0;
DECLARE @p7 Int = 0;
;WITH CTEVehCatId
AS (SELECT value
FROM STRING_SPLIT(@vehicleCategoryIds, ',')
)
Select tblDer.Id , tblDer.FuelTypeText, tblDer.TransmissionText, tblDer.Derivative, tblDer.FleetPrice, tblDer.GroupName,
tblDer.GroupId, tblDer.SWRate,tblDer.LWRate,tblDer.Tariff,tblDer.PassengerSeats, tblDer.GroupIcon, tblDer.Category,
tblDer.OrderNo, tblDer.IsEnquiry, tblDer.IsBooking, tblDer.IsGhost
FROM
(Select f.Id Id, (select top 1 FuelType FROM FleetFuelTypes (nolock) where Fleet_Id = f.Id ) FuelTypeText,
(select top 1 Transmission FROM FleetTransmissions (nolock) where Fleet_Id = f.Id ) TransmissionText,
f.Derivative Derivative,b.BandPrice FleetPrice, g.Name GroupName, g.Id GroupId,
isnull(t.SWRate,0) SWRate,isnull(t.LWRate,0) LWRate,t.Id Tariff, isnull(f.PassengerSeats,0) PassengerSeats,g.Icon GroupIcon,
c.Name Category, g.OrderNo, [g].IsEnquiry, [g].IsBooking, [g].IsGhost,
ROW_NUMBER() over (partition by f.Id order by g.OrderNo desc) rowNum
FROM [Tariff] AS [t] with (nolock)
INNER JOIN [VehicleCategories] AS [c] with (nolock) ON [t].[FleetCategory_Id] = [c].[Id]
INNER JOIN [FranchiseFrontendVehicleCategory] ffvc with (nolock) ON ffvc.[VehicleCategoryId] = [c].Id
INNER JOIN
(
Select tblVeh.* FROM
(Select [vg].*, fvg.FranchiseId , CAST(isnull(fvg.[IsEnquiry],0) as Bit) IsEnquiry, CAST(isnull(fvg.[IsBooking],0) as Bit) IsBooking,
CAST(isnull(fvg.[IsGhost],0) as Bit) IsGhost,
ROW_NUMBER() over (partition by vg.Id order by vg.Id) RowNo
FROM [VehicleGroups] (nolock) AS [vg]
left join [FranchiseVehicleGroups] fvg (nolock) on [vg].Id = fvg.VehicleGroupId --and fvg.FranchiseId = @franchiseId
Where [vg].StatusId = 1
) tblVeh Where tblVeh.RowNo = 1 and tblVeh.FranchiseId = @franchiseId
) AS [g] ON [t].[Group_Id] = [g].[Id]
-- [VehicleGroups] (nolock) AS [g] ON [t].[Group_Id] = [g].[Id]
INNER JOIN [BandRates] (nolock) AS [b] ON [t].[Id] = [b].[Tariff_Id]
INNER JOIN [Fleets] (nolock) AS [f] ON [t].[Id] = [f].[Tariff_Id]
LEFT OUTER JOIN [FleetDisposal] (nolock) AS [d] ON [f].[Id] = [d].[Fleet_Id]
WHERE ffvc.[FranchiseId] = @franchiseId AND isnull(ffvc.IsBooking,0) = 1
AND ([t].[Franchise_Id] = @franchiseId) AND ([t].[FleetCategory_Id] in (Select value from CTEVehCatId)) AND ([f].[CoreFleet] = @p2) AND ([f].[StatusId] = @p3)
AND (((([d].[Fleet_Id]) IS NULL) AND ([f].[WebEnabled] = @p4) AND (NOT ([f].[GhostVehicle] = 1))) OR ([f].[GhostVehicle] = 1))
-- AND [g].ShowOnWeb = 1
) tblDer Where tblDer.rowNum = 1 and (tblDer.IsEnquiry = 1 OR tblDer.IsBooking = 1)
ORDER BY [tblDer].[OrderNo];
Please focus on this part:
([t].[FleetCategory_Id] in (Select value from CTEVehCatId))
@vehicleCategoryIds have '1,8';
See the plan at here
https://www.brentozar.com/pastetheplan/?id=B11apdVvo
If I use this directly equal operator then it works
[t].[FleetCategory_Id] = @vehicleCategoryId
This works perfectly
but why not with this expression
([t].[FleetCategory_Id] in (Select value from CTEVehCatId))