Difference between these 2 SQL

This is SQL 2014 and it's related to the question here
https://www.experts-exchange.com/questions/29129110/Replace-works-but-is-there-a-better-way.html

And this https://www.experts-exchange.com/questions/29129724/Result-of-these-2-SQLs.html  (I got the "exists" line of code from this solution)

I'm using the solution in that question but wondering why I'm not getting the correct number of rows.

1. Please see attached for a sample data. It creates a table with some data in it.

2. This is the code I'm using. I've noted in it which one is correct and which one is not correct. I don't know what I'm missing.

DECLARE @results TABLE(SearchText NVARCHAR(4000))
DECLARE  @searchtext nvarchar(4000)
DECLARE  @searchtext2 nvarchar(4000)

SET @searchtext = '%019%'
SET @searchtext2 = '%O19%'

INSERT INTO @results --*************  0 and O C# code has for SerialNo. tfs892
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')

	SELECT * FROM @results

SELECT * FROM dbo.camillatest u
WHERE 
exists (Select 1 from @results r WHERE u.SerialNo LIKE @searchtext) --**** the solution I'm using. It brings back 27277 but it should be 27340
--(u.SerialNo LIKE @searchtext OR u.SerialNo LIKE @searchtext2) -- 27340 correct rows 
--(u.SerialNo LIKE '%019%' OR u.SerialNo LIKE '%O19%') -- 27340 correct row

Open in new window

sample-data-for-ee.txt
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.

slightwv (䄆 Netminder) Commented:
>>It brings back 27277 but it should be 27340

I don't know what you mean by 27277 or 27340.  I don't see those IDs in your sample data.
slightwv (䄆 Netminder) Commented:
OH, you mean # of rows....  let me look.
slightwv (䄆 Netminder) Commented:
If I hard-code the values using my test table from your data, I get 27338 rows.
SELECT * FROM tab1 u
WHERE (u.SerialNo like '%019%' OR u.SerialNo LIKE '%O19%');

Open in new window

Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

slightwv (䄆 Netminder) Commented:
I'm also not sure what you are doing with the replaces in the union statement.  They return the same two distinct values so it doesn't seem worth it.

If you run this, I get the two original values:
	SELECT distinct searchtext FROM @results

Open in new window

CamilliaAuthor Commented:
Yes, number of rows. I don't get the correct number of rows when I want to use that #results table but it should be the same as when I hardcode.

27338 rows but now try the #results table. Don't think we get the same # of rows.
CamilliaAuthor Commented:
I'm also not sure what you are doing with the replaces in the union statement.  They return the same two distinct values so it doesn't seem worth it.

I need to search for %019% and %O19%
slightwv (䄆 Netminder) Commented:
>>I need to search for %019% and %O19%

I get that.  You should just insert the two values.  The extra replace statements add nothing.


Using your two selects, I get the same 27338 rows:
SELECT count(*) FROM tab1 u
WHERE (u.SerialNo like '%019%' OR u.SerialNo LIKE '%O19%');

SELECT count(*) FROM tab1 u
where exists (Select 1 from results r WHERE u.SerialNo LIKE r.searchtext);

Open in new window


My results table:
SQL> select * from results;

SEARCHTEXT
--------------------------------------------------
%019%
%O19%

Open in new window

CamilliaAuthor Commented:
I'll try it . I wonder if that would make a difference in why "exists" brings back different rows than when i have the values hardcoded.
CamilliaAuthor Commented:
If you look at the related questions (first one), I do need all the unions. User might enter 0O19 or O019. That's the reason for the 3 unions...Orig value and the replacements.

Even with the change , why doesn't that line of code bring back correct number of rows?
slightwv (䄆 Netminder) Commented:
Let me set up a test on my sql server express.
ste5anSenior DeveloperCommented:
Because you're looking for different predicates. Just format your code and take a look at the relevant two SELECT statements:

-- Incorrect
SELECT COUNT(*)
FROM dbo.camillatest u
WHERE EXISTS
(
    SELECT  *
    FROM    @results r
    WHERE   u.SerialNo LIKE @searchtext
);

-- Correct
SELECT COUNT(*)
FROM dbo.camillatest u
WHERE EXISTS
(
    SELECT  *
    FROM    @results r
    WHERE   u.SerialNo LIKE @searchtext
        OR u.SerialNo LIKE @searchtext2
);

Open in new window

You're using one predicate in the first case, but two in the second case. Thus the difference in the result. The key point here is: the where predicate relates to the outer SELECT only. The inner SELECT in the EXISTS is just a weird kind of doing it. Your two queries are equivalent to

-- Incorrect
SELECT COUNT(*)
FROM dbo.camillatest u
WHERE   u.SerialNo LIKE @searchtext;

-- Correct
SELECT COUNT(*)
FROM dbo.camillatest u
WHERE   u.SerialNo LIKE @searchtext
    OR u.SerialNo LIKE @searchtext2

Open in new window


btw, what is the purpose the @results table variable? Do you want to use it as filter table? Then you need a correlated EXISTS:

SELECT COUNT(*)
FROM dbo.camillatest u
WHERE EXISTS
(
    SELECT  *
    FROM    @results r
    WHERE   u.SerialNo LIKE R.SearchText
);

Open in new window

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
slightwv (䄆 Netminder) Commented:
Go with what ste5an posted.  He's a SQL Server person.  I'm an Oracle person trying to learn my way around SQL Server.

I wanted to followup with my test.  My SQL Server skills are pretty much non-existent so I made results a permanent table:
1> select cast(searchtext as varchar(10)) from results;
2> go

----------
%019%
%O19%
%019%

Open in new window


I run your exists and get 27340:
1> SELECT count(*) FROM dbo.camillatest u
2> WHERE
3> exists (Select 1 from results r WHERE u.SerialNo LIKE searchtext)
4> go

-----------
      27340

(1 rows affected)

Open in new window

CamilliaAuthor Commented:
Let me try it. (I'll work on it tonight or this weekend and post back. I want to have it for Monday morning for my manager to review.)
PortletPaulEE Topic AdvisorCommented:
I believe your query in the question has some misunderstandings that leads to the different results.

Use this as the benchmark:
SELECT
       count(*) c_all
     , count(case when u.SerialNo LIKE '%O19%' then 1 end) O19
FROM dbo.camillatest u
WHERE (u.SerialNo LIKE '%019%' OR u.SerialNo LIKE '%O19%')

Open in new window


Now run this variant of your original:
DECLARE @results TABLE(SearchText NVARCHAR(4000))
DECLARE @searchtext nvarchar(4000)
DECLARE @searchtext2 nvarchar(4000)

SET @searchtext = '%019%'
SET @searchtext2 = '%O19%'

INSERT INTO @results
SELECT @searchtext UNION ALL
SELECT @searchtext2

SELECT
       count(*) c_all
     , count(case when u.SerialNo LIKE '%O19%' then 1 end) O19
FROM camillatest u
WHERE EXISTS (
        Select 1 
        from @results r 
        WHERE u.SerialNo LIKE @searchtext or u.SerialNo LIKE @searchtext2 -- here is the major difference
        )
;        
 

Open in new window

When "It brings back 27277 but it should be 27340", you have not included BOTH @searchtext & @searchtext2 inside the exists subquery

HOWEVER what I think you really need to do is use the COLUMN r.SearchText instead of the parameters
DECLARE @results TABLE(SearchText NVARCHAR(4000))
DECLARE @searchtext nvarchar(4000)
DECLARE @searchtext2 nvarchar(4000)

SET @searchtext = '%019%'
SET @searchtext2 = '%O19%'

INSERT INTO @results
SELECT @searchtext UNION ALL
SELECT @searchtext2

SELECT
       count(*) c_all
     , count(case when u.SerialNo LIKE '%O19%' then 1 end) O19
FROM camillatest u
WHERE EXISTS (
        Select 1 
        from @results r 
        WHERE u.SerialNo LIKE r.[SearchText] -- this is what you want to use I think
        )
;        

Open in new window

see this demo
CamilliaAuthor Commented:
When I use the #results table, I don't need to use @seatctext2 anymore. I'll turn on my work laptop and try what you guys have.

I'll post back.
PortletPaulEE Topic AdvisorCommented:
>>"When I use the #results table, I don't need to use @seatctext2 anymore."

Correct!

The purpose of #results is to allow comparisons against the values held in that table variable.
slightwv (䄆 Netminder) Commented:
I've tested both based on my physical results table:
SELECT count(*) FROM dbo.camillatest u
WHERE 
exists (Select 1 from results r WHERE u.SerialNo LIKE searchtext)


SELECT count(*) FROM dbo.camillatest u
WHERE (u.SerialNo LIKE '%019%' OR u.SerialNo LIKE '%O19%')

Open in new window


I get 27340 for both so I have to assume there is a problem in your results TEMP table.

If you are getting different results, you might look at actually seeing what the differences are and that will likely help you narrow down where the problem is.

Look at EXCEPT queries.

Take the query returning more rows and use it first.  Add EXCEPT and the query returning less rows.  See what comes back.  That should tell you the "why" it is happening.  My guess is the result table doesn't have everything you think it should have.

SELECT SerialNo FROM dbo.camillatest u
WHERE (u.SerialNo LIKE '%019%' OR u.SerialNo LIKE '%O19%')
EXCEPT
SELECT SerialNo FROM dbo.camillatest u
WHERE 
exists (Select 1 from results r WHERE u.SerialNo LIKE searchtext)

Open in new window

slightwv (䄆 Netminder) Commented:
Interesting thoughts that I cannot explain but it's close enough to ponder:
I take your correct numbers and subtract the "bad" numbers I get 63 different:  27340 - 27277 = 63

I look at rows that match %O19%, I see 66 rows:
select SerialNo from dbo.camillatest  where serialno like '%O19%'

Open in new window


The numbers are close enough to make me wonder if you are somehow missing the 'O19' (Oh-19) from your results table from the original counts you posted?

I cannot explain where the 3 missing ones are from your query and the sample data you posted.

If you can post results from the actual data you posted, we can go from there.
CamilliaAuthor Commented:
Let me try all this again and I'll post back.
CamilliaAuthor Commented:
r.[SearchText]

Yes, I had @searchtext and  I needed searchtext from Results table. Totally missed that. Thanks for the help, as always.
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.