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

Camillia
Camillia used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
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).
Thanks, Dustin. Let me see.
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Co-Founder and Chief Architect
Top Expert 2016
Commented:
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.
Oh, let me see. Thanks for taking the time to look into this with me.
I'm going to run another test. I'll post back.
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 :)
Thank you so much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial