Why In operator is not working as expecting ?

Question

Why In operator is not working as expecting ?

mehmood tekfirst 771

Hi,

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

1). FleetFuelTypes
Structure

CREATE TABLE [dbo].[FleetFuelTypes](  
 [Id] [smallint] IDENTITY(1,1) NOT NULL,  
 [FuelType] [nvarchar](50) NOT NULL,  
 CONSTRAINT [PK_FleetFuelType] PRIMARY KEY CLUSTERED   
(  
 [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY]  
GO

Data:

Id     FuelType  
1       Petrol  
2      Diesel  
3      HYBRID ELECTRIC  
4       ELECTRIC  
5      GAS BI FUEL  
6      ELECTRIC DIESEL  
7        GAS

2).

CREATE TABLE [dbo].[FleetTransmissions](  
 [Id] [smallint] IDENTITY(1,1) NOT NULL,  
 [Transmission] [nvarchar](50) NOT NULL,  
 CONSTRAINT [PK_FleetTransmissions] PRIMARY KEY CLUSTERED   
(  
 [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY]  
GO  

Id Transmission  
1 Automatic  
2 Manual  
3 SEMI AUTO  
4 CVT

3). Tariff

CREATE TABLE [dbo].[Tariff](  
 [Id] [int] IDENTITY(1,1) NOT NULL,  
 [Franchise_Id] [int] NOT NULL,  
 [Group_Id] [int] NOT NULL,  
 [FleetCategory_Id] [smallint] NOT NULL,  
 CONSTRAINT [PK_Tarrif] PRIMARY KEY CLUSTERED   
(  
 [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY]  
GO  

Id        Franchise_Id      FleetCategory_Id  
14273 16367                 2  
14272 16367                 2  
14271 16367                 2  
14270 16367                 3  
14269 16367                 2  
14268 16367                 2  
14267 16367                1  
14266 16367                1  
14265 16367                1  
14264 16367                1

4). VehicleCategories

 CREATE TABLE [dbo].[VehicleCategories](  
     [Id] [smallint] NOT NULL,  
     [Name] [nvarchar](50) NOT NULL,      
     CONSTRAINT [PK_VehicleCategories] PRIMARY KEY CLUSTERED   
    (  
     [Id] ASC  
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
    ) ON [PRIMARY]  
    GO  

Id     Name  
1       Car  
8      HP Cars  
5      Minibus  
7      Motorhome 3.5T-7.5T  
6      Motorhome up to 3.5T  
4      MPV  
3      Van 3.5T-7.5T  
2      Van up to 3.5T

5). FranchiseFrontendVehicleCategory

CREATE TABLE [dbo].[FranchiseFrontendVehicleCategory](  
[Id] [int] IDENTITY(1,1) NOT NULL,  
[FranchiseId] [int] NOT NULL,  
[VehicleCategoryId] [smallint] NOT NULL,  
[IsBooking] [bit] NOT NULL,  
 CONSTRAINT [PK_FranchiseFrontendVehicleCategory] PRIMARY KEY CLUSTERED   
(  
[Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO  

Id FranchiseId VehicleCategoryId IsBooking  
9      16367             1                        1  
10   16367              2                        1  
11    16367             3                        1  
12   16367              4                        1  
13   16367              5                        1  
14   16367              6                        1  
15   16367              7                        1  
16   16367             8                         1

6). VehicleGroups

CREATE TABLE [dbo].[VehicleGroups](  
 [Id] [int] IDENTITY(1,1) NOT NULL,  
 CONSTRAINT [PK_VehicleGroups] PRIMARY KEY CLUSTERED   
(  
 [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO  

Id  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
2039  
2043  
2044  
2045  
2046  
2047  
2048  
2049  
2050  
2052  
2053  
2054  
2055  
2056  
2057  
2058  
2059  
2060  
2061  
2062  
2063  
2064  
2065  
2066  
2067  
2068  
2069  
2070  
2071  
2072  
2073  
2074  
2075  
2076  
2077  
2078  
2079  
2080  
2081  
2082  
2083  
2084  
2085  
2086  
2087  
2088  
2089  
2090  
2091  
2092  
2094  
2095  
2096  
2097  
2098  
2099  
2100  
2101  
2102  
2103  
2104  
2105  
2106  
2107  
2108  
2109  
2110  
2111  
2112  
2113  
3110  
3111  
3112  
3113  
3114  
3115  
3116  
3117  
3118  
3119  
3120  
3121  
3122  
3123

7). FranchiseVehicleGroups

CREATE TABLE [dbo].[FranchiseVehicleGroups](  
 [Id] [int] IDENTITY(1,1) NOT NULL,  
 [FranchiseId] [int] NOT NULL,  
 [VehicleGroupId] [int] NOT NULL,  
 [IsEnquiry] [bit] NOT NULL,  
 [IsBooking] [bit] NOT NULL,  
 CONSTRAINT [PK_FranchiseVehicleGroups] PRIMARY KEY CLUSTERED   
(  
 [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO  

Id FranchiseId VehicleGroupId IsEnquiry IsBooking  
33 16367             18                          1       1  
34 16367           2044                        1       1  
35 16367            15                           1       1  
36 16367            16                           1       1  
37 16367            20                           1       1  
38 16367          2045                         1       1  
39 16367            17                           1       1  
40 16367            19                           1       1  
41 16367            22                           1       1  
42 16367            21                           1       1  
43 16367          2043                         1       1  
44 16367           25                            1       1

8). BandRates

CREATE TABLE [dbo].[BandRates](  
 [Id] [int] IDENTITY(1,1) NOT NULL,  
 [Tariff_Id] [int] NOT NULL,  
 CONSTRAINT [PK_BandRates] PRIMARY KEY CLUSTERED   
(  
 [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY]  
GO  

Id        Tariff_Id  
34323 14264  
34324 14264  
34325 14264  
34326 14264  
34327 14264  
34328 14264  
34329 14264  
34330 14264  
34331 14264  
34332 14264  
34333 14265  
34334 14265  
34335 14265  
34336 14265  
34337 14265  
34338 14265  
34339 14265  
34340 14265  
34341 14265  
34342 14265  
34343 14266  
34344 14266  
34345 14266  
34346 14266  
34347 14266  
34348 14266  
34349 14266  
34350 14266  
34351 14266  
34352 14266  
34353 14267  
34354 14267  
34355 14267  
34356 14267  
34357 14267  
34358 14267  
34359 14267  
34360 14267  
34361 14267  
34362 14267  
34363 14268  
34364 14268  
34365 14268  
34366 14268  
34367 14268  
34368 14268  
34369 14268  
34370 14268  
34371 14268  
34372 14268  
34373 14269  
34374 14269  
34375 14269  
34376 14269  
34377 14269  
34378 14269  
34379 14269  
34380 14269  
34381 14269  
34382 14269  
34383 14270  
34384 14270  
34385 14270  
34386 14270  
34387 14270  
34388 14270  
34389 14270  
34390 14270  
34391 14270  
34392 14270  
34393 14271  
34394 14271  
34395 14271  
34396 14271  
34397 14271  
34398 14271  
34399 14271  
34400 14271  
34401 14271  
34402 14271  
34403 14272  
34404 14272  
34405 14272  
34406 14272  
34407 14272  
34408 14272  
34409 14272  
34410 14272  
34411 14272  
34412 14272  
34413 14273  
34414 14273  
34415 14273  
34416 14273  
34417 14273  
34418 14273  
34419 14273  
34420 14273  
34421 14273  
34422 14273

9). Fleets

CREATE TABLE [dbo].[Fleets](  
 [Id] [int] IDENTITY(1,1) NOT NULL,  
 [Tariff_Id] [int] NOT NULL,  
 [CoreFleet] [bit] NULL,  
 [WebEnabled] [bit] NULL,  
 [Franchise_Id] [int] NOT NULL,  
 [StatusId] [int] NOT NULL,  
 [GhostVehicle] [bit] NOT NULL,  
 CONSTRAINT [PK_Fleets] PRIMARY KEY CLUSTERED   
(  
 [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY]  
GO  

	 Id    Tariff_Id      CoreFleet    WebEnabled                                      Franchise_Id      StatusId    GhostVehicle  
41596   14273			1			1					16367			1				0  
42902   14272			1			1					16367			1				0  
43115   14272			1			1					16367			1				0  
44446   14273			1			1					16367			1				0  
44701   14273			1			1					16367			1				0  
46965   14265			1			1					16367			1				0  
47582   14269			1			1					16367			1				0  
51301   14269			1			1					16367			1				0  
38605   14265			1			1					16367			4				0  
38606   14265			1			1					16367			4				0  
38607   14269			1			1					16367			4				0  
38608   14269			1			1					16367			4				0  
41597   14273			1			1					16367			4				0  
41598   14273			1			1					16367			4				0  
42920   14272			1			1					16367			4				0  
43210   14272			1			1					16367			4				0  
43420   14265			1			1					16367			4				0  
44306   14272			1			1					16367			4				0  
44948   14272			1			1					16367			4				0  
47205   14272			1			1					16367			4				0  
47243   14272			1			1					16367			4				0  
48402   14269			1			1					16367			4				0

10). FleetDisposal

CREATE TABLE [dbo].[FleetDisposal](  
 [Id] [int] IDENTITY(1,1) NOT NULL,  
 [Fleet_Id] [int] NOT NULL,  
 CONSTRAINT [PK_FleetDisposal] PRIMARY KEY CLUSTERED   
(  
 [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY]  
GO  

Id         Fleet_Id  
29143 38606  
31230 41597  
31231 41598  
31232 42920  
31233 43420  
31863 44306  
31864 43210  
31865 38608  
33376 38607  
33377 44948  
38427 48402  
38428 38605  
38429 47243  
38430 47205

Olaf Helper 47,586 Reputation points

2022-11-30T07:17:08.313+00:00

Your SQL statement is unreadable, screenshots are somehow useless and that's really to less on information.
Please post table design as DDL, some sample data as DML statement to reproduce the issue and the expected result.
mehmood tekfirst 771 Reputation points

2022-11-30T07:31:10.483+00:00

ok i try to add
mehmood tekfirst 771 Reputation points

2022-11-30T08:07:36.477+00:00

OK check now
mehmood tekfirst 771 Reputation points

2022-11-30T13:17:24.017+00:00

thank you everyone, my issue has been fixed
Ronen Ariely 15,216 Reputation points

2022-12-17T17:23:51.087+00:00

Please provide some information about the solution for the next person that will come to this thread

In addition you need to close this thread, so mark your own answer as the answer in order to close the thread

1 answer

Your answer

Olaf Helper 47,586 Reputation points

2022-11-30T07:17:08.313+00:00

Your SQL statement is unreadable, screenshots are somehow useless and that's really to less on information.
Please post table design as DDL, some sample data as DML statement to reproduce the issue and the expected result.
mehmood tekfirst 771 Reputation points

2022-11-30T07:31:10.483+00:00

ok i try to add
mehmood tekfirst 771 Reputation points

2022-11-30T08:07:36.477+00:00

OK check now
mehmood tekfirst 771 Reputation points

2022-11-30T13:17:24.017+00:00

thank you everyone, my issue has been fixed
Ronen Ariely 15,216 Reputation points

2022-12-17T17:23:51.087+00:00

Please provide some information about the solution for the next person that will come to this thread

In addition you need to close this thread, so mark your own answer as the answer in order to close the thread

Answer 1

OK @Ronen Ariely

See my solution , this join clause was the solution

and fvg.FranchiseId = @franchiseId

If I remove this join clause then there is no result found

by using the below

  left join  [FranchiseVehicleGroups] fvg (nolock) on [vg].Id = fvg.VehicleGroupId and fvg.FranchiseId = @franchiseId

I thought the problem is in following line of code but it was not the case, problem was in join clause when I made another join clause data is being displayed accurately as we expected.

declare @vehicleCategoryIds nvarchar(100) = '1,8';  
  
;WITH CTEVehCatId  
  AS (SELECT value  
  FROM STRING_SPLIT(@vehicleCategoryIds, ',')  
  )  


AND ([t].[FleetCategory_Id] in (Select value from CTEVehCatId))

Share via

Why In operator is not working as expecting ?

1 answer

Your answer