Why do Sql Server In operator behaves differently and return nothing?

Question

Post reply

Why do Sql Server In operator behaves differently and return nothing?

mehmood

Old Hand

Points: 336

November 30, 2022 at 7:11 am

#4119886

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  ON [t].[Id] = .[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))

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Collins SSCrazy Points: 2072 More actions · Answer 2

Sorry not able to replicate the issue. The OP code relies on implicit conversion between INT and NVARCHAR which generally works but is an error prone (and awful for the next programmer to deal with) habit imo

declare @vehicleCategoryId int = '1';
declare @vehicleCategoryIds nvarchar(100) = '1,8';

;WITH CTEVehCatId
AS (SELECT value
    FROM STRING_SPLIT(@vehicleCategoryIds, ','))
select *
from (values (1), (2), (8)) v(num)
where v.num in(select value from CTEVehCatId);

;WITH CTEVehCatId
AS (SELECT value
    FROM STRING_SPLIT(@vehicleCategoryIds, ','))
select *
from (values (1), (2), (8)) v(num)
where v.num =@vehicleCategoryId;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

mehmood Old Hand Points: 336 More actions · Answer 3

Thank you everyonw.

my issue has been resolved.

by adding this

and fvg.FranchiseId = @franchiseId,

,

it works . may be there is some data issue in this table [FranchiseVehicleGroups].

This reply was modified 1 year, 5 months ago by mehmood.