Table field comparison

[shabar]

Hi All
I ran following query for field comparison in Tab1 and Tab2. Needs to check what are the Col1 values missing in
Tab2


SELECT Tab1.*


FROM Tab1 LEFT JOIN Tab2 IM ON Tab1.Col1 = Tab2.Col2
WHERE Tab2.Col2 Is Null;

Here count is 16177

When I run same query with NOT NULL as follows

SELECT Tab1.*
FROM Tab1 LEFT JOIN Tab2 IM ON Tab1.Col1 = Tab2.Col2
WHERE Tab2.Col2 Is not Null;

I got count as 3190200

But total of above two should tally to Tab1 total row count (i.e. 17683 as follows)

But When I check Tables row count seperately its as follws

Tab1 17683
Tab2 1642488


What could be the issue

Cheers

Shabar

[June7]

What is the 'IM' in the sql statement? Never seen that.

Could be duplicate values in one or both of the tables to cause the increased record count.

Try:

SELECT Tab1.Col1
FROM Tab1 LEFT JOIN Tab2 ON Tab1.Col1 = Tab2.Col2
WHERE Tab2.Col2 Is Null;

Or

SELECT Tab1.Col1
FROM Tab2 RIGHT JOIN Tab1 ON Tab2.Col2 = Tab1.Col1
WHERE Tab2.Col2 Is Null;

Both worked for me.

[shabar]

Thax June7

'IM' is a typo. Sorry about that

Since I do get duplicate in both tables this row count may appear. Anyway do you have idea how its arrived



Cheers

Shabar

[June7]

The duplicate records must each join to the corresponding record(s) in other table. This increases record count to greater than in each table alone. If the purpose is just to determine if Tab1.Col1 data is in Tab2.Col2 then the count might be irrelevant. Depends what you want to do.