Difference between these 2 SQL

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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

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.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
OH, you mean # of rows....  let me look.
Most Valuable Expert 2012
Distinguished Expert 2018

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

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!

Most Valuable Expert 2012
Distinguished Expert 2018

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

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.
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%
Most Valuable Expert 2012
Distinguished Expert 2018

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

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.
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?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Let me set up a test on my sql server express.
Senior Developer
Commented:
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

Most Valuable Expert 2012
Distinguished Expert 2018
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

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 Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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
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 Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
>>"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.
Most Valuable Expert 2012
Distinguished Expert 2018

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

Most Valuable Expert 2012
Distinguished Expert 2018

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.
Let me try all this again and I'll post back.
r.[SearchText]

Yes, I had @searchtext and  I needed searchtext from Results table. Totally missed that. Thanks for the help, as always.

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