May 15, 2008 at 2:14 pm
i have this query which runs good on sql server 2000:
select col1,col2 from table1,table2
where table1.col1*=table2.col2
am upgrading to sql 2005 and the database compatibility level will be 90, if i used left outer join i should get the same result right?
select col1,col2 from table1 left outer join table2
on table1.col1=table2.col2
is there any diffirence between these 2 queries?
..>>..
MobashA
May 15, 2008 at 2:23 pm
Nope, in that use they are equivalent.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 15, 2008 at 2:53 pm
is there any diffirence what so ever? in any case.
..>>..
MobashA
May 15, 2008 at 3:05 pm
if you later have
table1.col4 = table2.col4
in the where clause then it's an inner join
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 15, 2008 at 3:11 pm
select col1,col2 from table1,table2
where table1.col1*=table2.col2
You need to change this statement to (for 2005 and mode 90):
select col1,col2
from table1 left outer join table2 on table1.col1 = table2.col2
I have this all over my 2000 installation. Finding and correcting before moving to 2005 has been a challenge. The *= is not ANSI 92 compliant which mode 90 enforces.
May 15, 2008 at 3:16 pm
so if i have this
select col1,col2 from table1,table2
where table1.col1*=table2.col2
and table1.col4=table2.col4
who can i fix this?
..>>..
MobashA
May 15, 2008 at 3:23 pm
select col1,col2 from table1,table2
where table1.col1*=table2.col2
and table1.col4=table2.col4
This one would be:
select col1,col2
from table1
inner join table2 as innertable2 on table1.col4 = innertable2.col4
left outer join table2 as outertable2 on table1.col1 = outertable2.col2
May 15, 2008 at 3:32 pm
thanks i will try this one, hope it do the job.
..>>..
MobashA
May 16, 2008 at 6:24 am
I strongly recommend against using the old style joins. You really can run into trouble and they're not supported in every instance in SQL Server 2005 & above. See here for a few more details:
Oops. I'm an idiot. Sorry. Try this
http://qa.sqlservercentral.com/articles/Advanced+Querying/outerjoinmystery/2382/[/url]
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 16, 2008 at 6:31 am
the link wont open.
..>>..
MobashA
May 16, 2008 at 6:54 am
The main difference with the join-syntax is that you have to know
the bases of your join.
e.g
select A.col1
, B.colz
from tablea A
, tableb B
where A.col0 *= B.colx
and A.col3 > 8
Would that become
select A.col1
, B.colz
from tablea A
left join tableb B
on A.col0 *= B.colx
and A.col3 > 8
-- the col3 condition is strictly used to complete the join !
or would that become
select A.col1
, B.colz
from tablea A
left join tableb B
on A.col0 *= B.colx
where A.col3 > 8
-- The where clause is applied to the join-result.
Check the result sets !
The join-syntax differentiates join conditions and filter conditions.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 16, 2008 at 8:03 am
The true difficulty, Sybase or not, is that it is not ANSI 92 compliant. SQL Server 2005 operating in mode 90 enforces ANSI 92 standards so *= or =* will fail.
May 17, 2008 at 9:29 am
keep in mind the "old" syntax (*= / =*) has been anounced for deprecation since SQL7.0 sp2.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 17, 2008 at 2:31 pm
yes i know, but what can i do, they gave me the script and i have to make it work, i did tried to use left outer join but the query wont give the same result as before, i have to wait till i go back work and work on it.
if i couldn't fix it i will past the code here for ur advise thanks.
..>>..
MobashA
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply