Different result using "IN" query and "JOIN" query

Question

0.00/5 (No votes)

See more:

I've two tables t1 and t2.
From t1:-
total records sel_col-distinct
28564502 12257389

From t2:-
total records sel_col-distinct
11217091 10890077

I am executing the below queries:-

SQL

select count( distinct t1.sel_col ) distinct, count(sel_col) total
from 
t1(nolock) join t2(nolock)
on t1.sel_col = CONVERT(VARBINARY(max),t2.sel_col,2)

distinct total
10600479 25666519
time:-00:50 mins

SQL

SELECT count(distinct t1.sel_col) total, count(t1.sel_col) total FROM 
t1(nolock)
where t1.sel_col in 
(select CONVERT(VARBINARY(max),t2.sel_col,2) from t2(nolock))

distinct total
10600479 24835271
time:-12:54mins

The distinct value is coming same, but there is difference between total. The 2nd query is taking huge time. Is there any way to get the desired result? Also why there is so much difference in the total count?

Thanks in advance

Posted 2-Jan-14 9:02am

Member 10499886

Updated 2-Jan-14 10:15am

PIEBALDconsult

v2

Add a Solution

Comments

chaau 2-Jan-14 15:56pm

I guess you have rows in t2 with duplicate sel_col

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Stephen Hewison · Answer 1 · 2014-01-02T11:22:00

It's because the join has the ability to multiply rows. Look at the following tables:

TableA
Cust    Value 
1        1
1        2
2        1

TableB
Cust
1
1
2

When you join the two you get

a.Cust b.Cust Value
1      1      1
1      1      1
1      1      2
1      1      2
2      2      1

So if you count these values you get 5 where are using the in you get that same dataset as TableA so the count is 3.

If you run this SQL:

SQL

SELECT SUM(COUNT(*) - 1) diff, COUNT(*) total, sel_col FROM t2 GROUP BY sel_col HAVING COUNT(*) > 1

If this returns any results it means your count is being multiplied by the join. The diff column in the result will equal the difference between the two results.

If you need a join with the same result as the in, try the following.

SQL

SELECT COUNT(t1.sel_col) FROM t1 (NOLOCK) INNER JOIN (SELECT DISTINCT sel_col FROM t2 (NOLOCK)) t2

Joining to the sub query when the sub query is distinct will reduce duplicates and the multiplying effect of the join.

Christian Graus · Answer 2 · 2014-01-02T10:14:00

The reason they take different times is that the second uses a subquery, which is slow. Comparing varbinary(MAX) sounds like you're comparing files, which is always slow. Consider storing a hash of the data in your varbinary, for faster comparisons. You can then do a deeper comparison where the hashes match.

You are throwing the DISTINCT keyword about a bit. This often means that your SQL has other issues which you are hiding.

Are you saying the second result is correct ? What is the table structure ?

OK, I see now. You have put the totals there. Your first select is the same, it's a distinct. The second, is not. If you have the same value twice in t2, an inner join will create 2 copies of that row in your results, even if it's in t1, once. Doing a subquery, is not a join, so it would not create duplicate rows. This is, I assume, why you're using DISTINCT. Instead, you should fix your database structure.

Different result using "IN" query and "JOIN" query

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0