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

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%'

    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
    (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 
    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


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))