SQL returns the same amount of rows with and without the where clause

I can't figure out why this SQL brings back the same amount of rows with and without the where caluse (been at it for a day...took joins out, etc and I still can't figure it out). I'll keep at it and if I figure it out, I'll post back.

1. I can't attach the SQL here. It's too big to attach here. You can download it from here and create the table with the data. https://drive.google.com/open?id=1WVbU7EeC-r748RM8dVa74guQvR-DAxbk
   It's 55645 rows of data inserted into a table.

2. This works fine. Brings back 53678 rows with the "where" clause

DECLARE  @AssetType nvarchar(4000),
 @searchtext nvarchar(4000),
 @searchtext2 nvarchar(4000),
 @p__linq__0 VARCHAR(100) = N'N/A - Non-Managed'
 
 SET @AssetType =2
 SET @searchtext ='%019%' --'%MX7T11243364%'
 SET @searchtext2 ='%O19%'

 SELECT * 
--INTO camillaNoFTS
 FROM dbo.BigAssetSearch
 WHERE
 

  (
       ([Sched] LIKE @searchtext)
		 OR 
		  ([SerialNo] LIKE @searchtext)
		  or
		  ([SerialNo] LIKE @searchtext2)
	
        
        OR ([CustomerRef] LIKE @searchtext )
        OR ([Model] LIKE @searchtext )
        OR ([Manufacturer] LIKE @searchtext)
        OR ([SiteAddress] LIKE @searchtext )
        OR 
		(customername LIKE @searchtext )
        OR ([LesseeName] LIKE @searchtext )
         
	)
	AND
    (
              ([LesseeCode] IS NULL)
              OR (NOT (
                          ([LesseeCode] = @p__linq__0)
                          AND ((CASE
                                    WHEN ([LesseeCode] IS NULL) THEN
                                        CAST(1 AS BIT)
                                   ELSE
                                        CAST(0 AS BIT)
                                END
                               ) = (CASE
                                        WHEN (@p__linq__0 IS NULL) THEN
                                            CAST(1 AS BIT)
                                        ELSE
                                            CAST(0 AS BIT)
                                    END
                                   )
                              )
                      )
                 )
         )

Open in new window



3. This works fine when I remove the where clause. Has 55645 rows

DECLARE  @AssetType nvarchar(4000),
 @searchtext nvarchar(4000),
 @searchtext2 nvarchar(4000),
 @p__linq__0 VARCHAR(100) = N'N/A - Non-Managed'
 
 SET @AssetType =2
 SET @searchtext ='%019%' --'%MX7T11243364%'
 SET @searchtext2 ='%O19%'

 SELECT * 
--INTO camillaNoFTS
 FROM dbo.BigAssetSearch
 WHERE
 
--  id = 4379297 AND --***remove this
  (
       ([Sched] LIKE @searchtext)
		 OR 
		  ([SerialNo] LIKE @searchtext)
		  or
		  ([SerialNo] LIKE @searchtext2)
	
        
        OR ([CustomerRef] LIKE @searchtext )
        OR ([Model] LIKE @searchtext )
        OR ([Manufacturer] LIKE @searchtext)
        OR ([SiteAddress] LIKE @searchtext )
        OR 
		(customername LIKE @searchtext )
        OR ([LesseeName] LIKE @searchtext )
         
	)
	--AND
 --   (
 --             ([LesseeCode] IS NULL)
 --             OR (NOT (
 --                         ([LesseeCode] = @p__linq__0)
 --                         AND ((CASE
 --                                   WHEN ([LesseeCode] IS NULL) THEN
 --                                       CAST(1 AS BIT)
 --                                   ELSE
 --                                       CAST(0 AS BIT)
 --                               END
 --                              ) = (CASE
 --                                       WHEN (@p__linq__0 IS NULL) THEN
 --                                           CAST(1 AS BIT)
 --                                       ELSE
 --                                           CAST(0 AS BIT)
 --                                   END
 --                                  )
 --                             )
 --                     )
 --                )
 --        )

Open in new window


4. This one , with or without the same where caluse brings back 55645. Why? this isn't correct.

DECLARE  @AssetType nvarchar(4000)--, do we want to filter here ot in code? in code for now
DECLARE  @searchtext nvarchar(4000)
declare @searchtext2 nvarchar(4000)


 SET @AssetType = 'N/A - Non-Managed'
 --SET @searchtext = '%MX7T11243364%' --'%MX7T11243364%' --%5CB2190HMS% -- 019 and O19
 SET @searchtext = '%019%'
 SET @searchtext2 = '%O19%'

 --account for 0 and O that code has
--DECLARE @results TABLE(SearchText NVARCHAR(4000))

--INSERT INTO @results
--	SELECT @searchtext UNION ALL
--	SELECT REPLACE(@searchtext,'0','O') UNION ALL
--	SELECT REPLACE(@searchtext,'O','0')
-- ---

 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )


  CREATE TABLE #schedsearch
 (
   rowid INT PRIMARY KEY
 )

  CREATE TABLE #MLsearch
 (
   rowid INT PRIMARY KEY
 )


INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
  rowid

FROM

	 dbo.BigAssetSearch AS u

WHERE 

		  (u.[SerialNo] LIKE @searchtext)
		  OR
           (u.[SerialNo] LIKE @searchtext2)
        
          OR (u.[CustomerRef] LIKE @searchtext )
          OR (u.[Model] LIKE @searchtext )
          OR (u.[Manufacturer] LIKE @searchtext)
          OR (u.[SiteAddress] LIKE @searchtext )




INSERT INTO #schedsearch
(
    rowid
)
SELECT s.rowid
FROM dbo.BigAssetSearch s
LEFT OUTER JOIN #unitsearch us ON us.rowid = s.rowid
WHERE 
--us.rowid IS NULL
--AND  
------ *********** here. remove and add this where clause. Brings the same amount of rows
--(
--          (s.[LesseeCode] IS NULL) --generated by EF. Keep it
--          OR (NOT (
--                      (s.[LesseeCode] = @AssetType)
--                      AND ((CASE
--                                WHEN (s.[LesseeCode] IS NULL) THEN
--                                    CAST(1 AS BIT)
--                                ELSE
--                                    CAST(0 AS BIT)
--                            END
--                           ) = (CASE
--                                    WHEN (@AssetType IS NULL) THEN
--                                        CAST(1 AS BIT)
--                                    ELSE
--                                        CAST(0 AS BIT)
--                                END
--                               )
--                          )
--                  )
--             )
--      )
 --     AND
      (
          (s.[Sched] LIKE @searchtext)
      
          OR 
		  (s.[LesseeName] LIKE @searchtext )
      );



--INSERT INTO #MLsearch
--(
--    rowid
--)
--SELECT m.rowid
--FROM dbo.BigAssetSearch m
--LEFT OUTER JOIN #unitsearch us ON us.rowid = m.rowid 
--LEFT OUTER JOIN #schedsearch sc ON sc.rowid = m.rowid 
--WHERE
--us.rowid IS NULL
--AND 
--sc.rowid IS NULL
--and 
--		   (m.[CustomerName] LIKE @searchtext )



SELECT 
         COUNT(*)
FROM dbo.BigAssetSearch cst
INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID
WHERE EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR
    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)
	--OR EXISTS(SELECT 1 FROM #mlsearch WHERE rowid = cst.rowid)



/*
 drop table #unitsearch
 drop table #schedsearch

DROP TABLE #MLSearch
*/

Open in new window

LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dustin SaundersDirector of OperationsCommented:
WHERE EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR
    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)

Open in new window


This is basically the same as saying "always select", because you're selecting 1.

If you want to know if the cst.rowid was in #unitsearch or #schedsearch you can use IN.

WHERE cst.rowid IN (SELECT rowid FROM #unitsearch)
	OR cst.rowid IN (SELECT rowid FROM #schedsearch)

Open in new window


You could also di it with a JOIN.
LEFT JOIN #unitsearch u ON u.rowid = cst.rowid
LEFT JOIN #schedsearch s ON s.rowid = cst.rowid
WHERE u.rowid IS NOT NULL OR s.rowid IS NOT NULL

Open in new window


You can try both and check the execution plan to see which is less expensive (probably the second one).
CamilliaAuthor Commented:
Thanks, Dustin. Let me see.
CamilliaAuthor Commented:
No, that's not it, don't think so.

Since you have the data, Dustin, (I took the link to the data down, I can provide it again if requested)

Do this, if you have time

1. This gives 41879 rows. This corresponds to the number of rows in #unitsearch

DECLARE  @AssetType nvarchar(4000),
 @searchtext nvarchar(4000),
 @searchtext2 nvarchar(4000),
 @p__linq__0 VARCHAR(100) = N'N/A - Non-Managed'
 
 SET @AssetType =2
 SET @searchtext ='%019%' --'%MX7T11243364%'
 SET @searchtext2 ='%O19%'

 SELECT * 
--INTO camillaNoFTS
 FROM dbo.BigAssetSearch
 WHERE
 
--  id = 4379297 AND --***remove this
  (
       --([Sched] LIKE @searchtext)
		-- OR 
		  ([SerialNo] LIKE @searchtext)
		  or
		  ([SerialNo] LIKE @searchtext2)
	
        
        OR ([CustomerRef] LIKE @searchtext )
        OR ([Model] LIKE @searchtext )
        OR ([Manufacturer] LIKE @searchtext)
        OR ([SiteAddress] LIKE @searchtext )
  --      OR 
		--(customername LIKE @searchtext )
        --OR ([LesseeName] LIKE @searchtext )
         
	)
	--AND
 --   (
 --             ([LesseeCode] IS NULL)
 --             OR (NOT (
 --                         ([LesseeCode] = @p__linq__0)
 --                         AND ((CASE
 --                                   WHEN ([LesseeCode] IS NULL) THEN
 --                                       CAST(1 AS BIT)
 --                                   ELSE
 --                                       CAST(0 AS BIT)
 --                               END
 --                              ) = (CASE
 --                                       WHEN (@p__linq__0 IS NULL) THEN
 --                                           CAST(1 AS BIT)
 --                                       ELSE
 --                                           CAST(0 AS BIT)
 --                                   END
 --                                  )
 --                             )
 --                     )
 --                )
 --        )

Open in new window


2. Run this. Same SQL as above but I want to run it for Sched. Gives 14830 rows. This corresponds to #schedsearch temp table below

DECLARE  @AssetType nvarchar(4000),
 @searchtext nvarchar(4000),
 @searchtext2 nvarchar(4000),
 @p__linq__0 VARCHAR(100) = N'N/A - Non-Managed'
 
 SET @AssetType =2
 SET @searchtext ='%019%' --'%MX7T11243364%'
 SET @searchtext2 ='%O19%'

 SELECT * 
--INTO camillaNoFTS
 FROM dbo.BigAssetSearch
 WHERE
 
--  id = 4379297 AND --***remove this
  (
       ([Sched] LIKE @searchtext)
		-- OR 
		  --([SerialNo] LIKE @searchtext)
		  --or
		  --([SerialNo] LIKE @searchtext2)
	
        
    --    OR ([CustomerRef] LIKE @searchtext )
    --    OR ([Model] LIKE @searchtext )
    --    OR ([Manufacturer] LIKE @searchtext)
    --    OR ([SiteAddress] LIKE @searchtext )
  --      OR 
		--(customername LIKE @searchtext )
        OR ([LesseeName] LIKE @searchtext )
         
	)
	AND
    (
              ([LesseeCode] IS NULL)
              OR (NOT (
                          ([LesseeCode] = @p__linq__0)
                          AND ((CASE
                                    WHEN ([LesseeCode] IS NULL) THEN
                                        CAST(1 AS BIT)
                                    ELSE
                                        CAST(0 AS BIT)
                                END
                               ) = (CASE
                                        WHEN (@p__linq__0 IS NULL) THEN
                                            CAST(1 AS BIT)
                                        ELSE
                                            CAST(0 AS BIT)
                                    END
                                   )
                              )
                      )
                 )
         )

Open in new window


So far so good

3. Now the SQL that doesn't bring back the same rows. It might be the last join, as you say above but I didn't see a change.
 Run it. I have a count(*) from the temp tables.You see the same number of rows --> 41879 and 14830.
But the last SQL that joins brings back 55645 rows.

USE [MyCSI_Global]
GO

-- exec [dbo].[usp_SearchAssets] '%019%'


DECLARE  @AssetType nvarchar(4000)--, do we want to filter here ot in code? in code for now
DECLARE  @searchtext nvarchar(4000)
declare @searchtext2 nvarchar(4000)


 SET @AssetType = 'N/A - Non-Managed'
 --SET @searchtext = '%MX7T11243364%' --'%MX7T11243364%' --%5CB2190HMS% -- 019 and O19
 SET @searchtext = '%019%'
 SET @searchtext2 = '%O19%'

 --account for 0 and O that code has
--DECLARE @results TABLE(SearchText NVARCHAR(4000))

--INSERT INTO @results
--	SELECT @searchtext UNION ALL
--	SELECT REPLACE(@searchtext,'0','O') UNION ALL
--	SELECT REPLACE(@searchtext,'O','0')
-- ---

 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )


  CREATE TABLE #schedsearch
 (
   rowid INT PRIMARY KEY
 )

  CREATE TABLE #MLsearch
 (
   rowid INT PRIMARY KEY
 )


INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
  rowid

FROM

	 dbo.BigAssetSearch AS u

WHERE 

		  (u.[SerialNo] LIKE @searchtext)
		  OR
           (u.[SerialNo] LIKE @searchtext2)
        
          OR (u.[CustomerRef] LIKE @searchtext )
          OR (u.[Model] LIKE @searchtext )
          OR (u.[Manufacturer] LIKE @searchtext)
          OR (u.[SiteAddress] LIKE @searchtext )




INSERT INTO #schedsearch
(
    rowid
)
SELECT s.rowid
FROM dbo.BigAssetSearch s
--LEFT OUTER JOIN #unitsearch us ON us.rowid = s.rowid
WHERE 
--us.rowid IS NULL
--AND  
----let the code handle this. Big table will have managed and non-managed
(
          (s.[LesseeCode] IS NULL) --generated by EF. Keep it
          OR (NOT (
                      (s.[LesseeCode] = @AssetType)
                      AND ((CASE
                                WHEN (s.[LesseeCode] IS NULL) THEN
                                    CAST(1 AS BIT)
                                ELSE
                                    CAST(0 AS BIT)
                            END
                           ) = (CASE
                                    WHEN (@AssetType IS NULL) THEN
                                        CAST(1 AS BIT)
                                    ELSE
                                        CAST(0 AS BIT)
                                END
                               )
                          )
                  )
             )
      )
      AND
      (
          (s.[Sched] LIKE @searchtext)
      
          OR 
		  (s.[LesseeName] LIKE @searchtext )
      );



--INSERT INTO #MLsearch
--(
--    rowid
--)
--SELECT m.rowid
--FROM dbo.BigAssetSearch m
--LEFT OUTER JOIN #unitsearch us ON us.rowid = m.rowid 
--LEFT OUTER JOIN #schedsearch sc ON sc.rowid = m.rowid 
--WHERE
--us.rowid IS NULL
--AND 
--sc.rowid IS NULL
--and 
--		   (m.[CustomerName] LIKE @searchtext )


SELECT COUNT(*) FROM #unitsearch

SELECT COUNT(*) FROM #schedsearch

SELECT 
         COUNT(*)
FROM dbo.BigAssetSearch cst
--INNER JOIN dbo.Portfolio p ON p.ID = cst.PortfolioID
--WHERE EXISTS(SELECT 1 FROM #unitsearch WHERE rowid = cst.rowid) OR
--    EXISTS(SELECT 1 FROM #schedsearch WHERE rowid = cst.rowid)
WHERE cst.rowid IN (SELECT rowid FROM #unitsearch)
	OR cst.rowid IN (SELECT rowid FROM #schedsearch)
	--OR EXISTS(SELECT 1 FROM #mlsearch WHERE rowid = cst.rowid)



/*
 drop table #unitsearch
 drop table #schedsearch

DROP TABLE #MLSearch
*/

Open in new window



It should match this and bring back 53678. Maybe it is the join in the final SQL statement

DECLARE  @AssetType nvarchar(4000),
 @searchtext nvarchar(4000),
 @searchtext2 nvarchar(4000),
 @p__linq__0 VARCHAR(100) = N'N/A - Non-Managed'
 
 SET @AssetType =2
 SET @searchtext ='%019%' --'%MX7T11243364%'
 SET @searchtext2 ='%O19%'

 SELECT * 
--INTO camillaNoFTS
 FROM dbo.BigAssetSearch
 WHERE
 
--  id = 4379297 AND --***remove this
  (
       ([Sched] LIKE @searchtext)
		 OR 
		  ([SerialNo] LIKE @searchtext)
		  or
		  ([SerialNo] LIKE @searchtext2)
	
        
        OR ([CustomerRef] LIKE @searchtext )
        OR ([Model] LIKE @searchtext )
        OR ([Manufacturer] LIKE @searchtext)
        OR ([SiteAddress] LIKE @searchtext )
        OR 
		(customername LIKE @searchtext )
        OR ([LesseeName] LIKE @searchtext )
         
	)
	AND
    (
              ([LesseeCode] IS NULL)
              OR (NOT (
                          ([LesseeCode] = @p__linq__0)
                          AND ((CASE
                                    WHEN ([LesseeCode] IS NULL) THEN
                                        CAST(1 AS BIT)
                                    ELSE
                                        CAST(0 AS BIT)
                                END
                               ) = (CASE
                                        WHEN (@p__linq__0 IS NULL) THEN
                                            CAST(1 AS BIT)
                                        ELSE
                                            CAST(0 AS BIT)
                                    END
                                   )
                              )
                      )
                 )
         )

Open in new window

Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

Dustin SaundersDirector of OperationsCommented:
So the difference is that you aren't applying the same filters to #unit and #sched as your big one-- so you are getting more RowIds.

So here, I'll break it up so you can play with what's coming back easier.  Lets apply the same WHERE filtering to each-- only changing the Sched/Serial No.

DECLARE  @AssetType nvarchar(4000)
DECLARE  @searchtext nvarchar(4000)
declare @searchtext2 nvarchar(4000)

SET @AssetType = 'N/A - Non-Managed'
SET @searchtext = '%019%'
SET @searchtext2 = '%O19%'

;WITH unit AS (
	SELECT RowId
	FROM BigAssetSearch
	WHERE
	 (
		(SerialNo LIKE @searchtext 
			OR 
			SerialNo LIKE @searchtext2)
		OR
		CustomerRef LIKE @searchtext
		OR
		Model LIKE @searchtext
		OR
		Manufacturer LIKE @searchtext
		OR 
		SiteAddress LIKE @searchtext
		OR
		CustomerName LIKE @searchtext
		OR
		LesseeName LIKE @searchtext
	)
	AND
	(
		LesseeCode IS NULL
		OR 
		(
			NOT (
				LesseeCode = @AssetType
				AND (
					ISNULL(LesseeCode,1) = ISNULL(@AssetType,1)
				)
			)
		)
	)
),
sched AS (
	SELECT RowId
	FROM BigAssetSearch
	WHERE
	 (
		(Sched LIKE @searchtext) 
		OR
		CustomerRef LIKE @searchtext
		OR
		Model LIKE @searchtext
		OR
		Manufacturer LIKE @searchtext
		OR 
		SiteAddress LIKE @searchtext
		OR
		CustomerName LIKE @searchtext
		OR
		LesseeName LIKE @searchtext
	)
	AND
	(
		LesseeCode IS NULL
		OR 
		(
			NOT (
				LesseeCode = @AssetType
				AND (
					ISNULL(LesseeCode,1) = ISNULL(@AssetType,1)
				)
			)
		)
	)
)
SELECT *
FROM unit u

Open in new window


This is all the ids from Unit, now with the same filter applied, 33912 rows.

Change the select to
SELECT * FROM sched s

Open in new window


This is all the Ids that match that, 26401 rows.  Now, see how many same:
SELECT *
FROM sched s
INNER JOIN unit u ON s.RowId = u.RowId

Open in new window

Gives back 12635 rows.

33912 + 26401 - 12635 gives us the expected rows.

So on your #unit and #sched tables, you need to have those same filters in place to get the same result.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
Oh, let me see. Thanks for taking the time to look into this with me.
CamilliaAuthor Commented:
I'm going to run another test. I'll post back.
CamilliaAuthor Commented:
Yes, now  I know what you mean by

you need to have those same filters in place to get the same result.

This is what I did:

1. The SQL that returns correct rows. I dumped the results into a table.
2. The SQL that doesn't return correct rows. I dumped the results into a table
3. Gor the difference, 1967 rows, and dumped these into a third table
4. Ran the actual SQL on the third table that has 1967 rows.
5. Yes, the "where" clause  on #sched does filter. It doesn't filter on #unit and I get extra rows back.

Makes sense now. Thanks for your help. Now, I can move on with my life :)
CamilliaAuthor Commented:
Thank you so much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.