Issue in Query

Question

Issue in Query

CDBA

Hall of Fame

Points: 3266
More actions
November 13, 2007 at 12:38 am

#62629

Hi
Below query is returning all rows of left table and matching rows of right table:
Select b.Col1, b.Col2, a.Col1
from Tab1 a join Tab2 b
on a.Col2 = b.Col2
As per my understanding, it must do a ineer join and should return only the rows where Col2 value are common in both tables. But it is behaving in different way (Returnibg all values of Tab1 and then matching values of both table)
Any suggestion plz how to resolve it?
Thanks in advance
Regards
Utsab Chattopadhyay

CDBA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 1

Sounds to me like Tab1 is nothing more than a subset of Tab2... that would cause such a return even though only an inner join were present.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CDBA Hall of Fame Points: 3266 More actions · Answer 2

Hi Jeff

Thanks.. Is there any way I could get the expected output?

Many thanks in advance.

Regards

Utsab Chattopadhyay

CDBA

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Reckon Jeff's right.

Try this...

Select b.Col1, b.Col2, a.Col1, a.Col2

from Tab1 a join Tab2 b

on a.Col2 = b.Col2

... don't think you will find any rows where b.Col2 and a.Col2 don't match.

Then find rows in Tab1 which are not in Tab2:

SELECT b.Col1, b.Col2, a.Col1, a.Col2

FROM Tab1 a LEFT JOIN Tab2 b ON a.Col2 = b.Col2

WHERE b.Col2 IS NULL

And finally to confirm, find rows in Tab2 which are not in Tab1:

SELECT b.Col1, b.Col2, a.Col1, a.Col2

FROM Tab2 a LEFT JOIN Tab1 b ON a.Col2 = b.Col2

WHERE b.Col2 IS NULL

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden