table Joins and overby clause

Question

Post reply

table Joins and overby clause

sanandh87

Mr or Mrs. 500

Points: 578
More actions
March 15, 2013 at 12:25 am

#394399

Hi,
Please find below query,
tab1 - Col1 int, Col2 varchar2(30)
tab2 - Col1 int, col2 varchar2(30) , col3 datetime
Query 1:
select a.col1,a.col2,b.col3
from tab1 a
full outer join tab2 b
on a.col1=b.col1
and b.col3 > '19000101'
Query 2:
select a.col1,a.col2,b.col3
from tab1 a
full outer join (select col1,col2,col3,
row_number() over(partition by col1,col3 order by col3) as rownumber
from tab2) b
on a.col1=b.col1
and b.col3 > '19000101'
Please see the above two queries,
Issue : I am getting row difference between this two queries.
for example while executing first query , getting output 3 rows.
while executing second query , getting output 5 rows.
Can you please explain me?

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

can you provide the sample data to which is being in used in above queries

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

Looks like whatever is causing the inconsistency has been lost in the simplification process. Try this:

-- Query 3:

SELECT a.col1, a.col2, b.col3

FROM tab1 a

FULL OUTER JOIN (

SELECT

col1,

col2,

col3--,

--row_number() over(partition by col1,col3 order by col3) as rownumber

FROM tab2

) b

ON a.col1 = b.col1

AND b.col3 > '19000101'

Desimplified, of course.

^{“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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Addtionally why are you using row_number() over(partition by col1,col3 order by col3) as rownumber

if you are not using it ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ln74077 Newbie Points: 9 More actions · Answer 4

Hi Sanandh ,

I Don't See any difference between two sql queries .I want to know what sample data are you using.

create table tab1(id int,name varchar(30));

create table tab2(id int,namel varchar(30),bdate datetime);

insert into tab1 values(1,'a'),(2,'b'),(3,'c');

insert into tab2 values(1,'z','2000-01-01'),(2,'y','2009-09-09'),(3,'x','2008-02-02');

select * from tab1 a full outer join tab2 b on a.id=b.id and b.bdate>'20050909'

select * from tab1 a full outer join (select id,bdate,namel,row_number() over(partition by id,bdate order by namel) as rownumber from tab2) b on a.id=b.id and b.bdate>'20050909';

select * from tab1;

select * from tab2;

select id,bdate,namel,row_number() over(partition by id,bdate order by namel) as rownumber from tab2 a;

select a.id,a.name,b.bdate from tab1 a full outer join tab2 b on a.id=b.id and b.bdate>'20050909';

select a.id,a.id,b.bdate from tab1 a full outer join (select id,bdate,namel,row_number() over(partition by id,bdate order by namel) as rownumber from tab2) b on a.id=b.id and b.bdate>'20050909';