problem with old style joins *=

Question

Post reply

problem with old style joins *=

nmederich

Old Hand

Points: 347
More actions
October 15, 2003 at 9:44 am

#160174

I have a select query that gives inconsistent result sets that is based on tables with static data. Output of either 7 rows or 83050 rows.
This query (included below) will return 2 different result sets that appears to happen randomly or maybe it depends on the lunar alignment and how many doughnuts I've eaten recently. Seriously, the only thing I could think of was to rewrite the query using the new style syntax of joins.
When I rewrote the query using the new join syntax (ansi standard left joins and inner joins), I get consistent results every time. This is unsettling because if old style queries are not returning the same results everytime then I have a lot of conversions to do in a hurry.
Has anyone experienced flakiness with SQL2000 SP3 and the old style joins?
SELECT
b.tot_deps_growth_rate_12
FROM book_values_dt b, book_values_dt b1, book_values_dt b2, client_info c
WHERE
b.client_nbr *= b1.client_nbr
AND b.client_nbr *= b2.client_nbr
AND b.svc *= b1.svc AND b.svc *= b2.svc
AND b.segment *= b1.segment AND b.segment *= b2.segment
AND b.period =CASE WHEN b.client_nbr IN (5988,23042,24438) THEN 200306 ELSE 200303 END
AND b1.period =CASE WHEN b1.client_nbr IN (5988,23042,24438) THEN 200212 ELSE 200209 END
AND b2.period =CASE WHEN b2.client_nbr IN (5988,23042,24438) THEN 200206 ELSE 200203 END
AND b.client_nbr = c.client_nbr --remove *
AND b.category =1
AND b.segment = 0 AND b.svc = 'BC'
AND c.client_nbr IN
(5988,
7462,
24382,
24438,
61435,
24317,
23042)
ORDER BY b.short_name

Viewing 14 posts - 1 through 13 (of 13 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Check out the Books OnLine. Specifically, use the Index search tab and enter:

Joins-SQL Server. Then double click on the sub-topic Transact-SQL.

That explains that using =* or *= can cause ambiguity.

-SQLBill

gljjr SSChampion Points: 10080 More actions · Answer 2

I first noticed that happen in SQL 7. I have been using the SQL-92 syntax JOIN syntax ever since. Once you get used to writing your queries this way you will find them much easier to read and write.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

andrewpenman Valued Member Points: 70 More actions · Answer 3

Agree.

Always use the SQL92 syntax. Outer joins weren't standardized before this and can return unusual results. -down to the fact that the WHERE clause is not associative. See BOL.

johnsonj SSCrazy Points: 2159 More actions · Answer 4

I ran into the problem when we first went to 2000. I found the sections in the BOL that basically tell you not to use them.

Basically, this is another great reason to have SP's instead of queries in the client apps (whether fat client or web). You only have one place to change the code. Of course, you may have a boat-load of SP's to change...

Joe Johnson

NETDIO,LLC.

Joe Johnson
NETDIO,LLC.

rstone SSCertifiable Points: 6209 More actions · Answer 5

The conversion might be tricky. In the old style, the where clause is applied before the join while in the new style the where is applied after the join. This can make a big difference in outer joins. The On clause can include part of the where clause to reduce the records before the join; however, I’m often surprised with the results. And watch out for null values in the On clause. A join with a derived table with the appropriate where clause might be one alternative.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 6

I have just spend a month removing those from SP's. Other no-no's for 2000 are <> Null or = NULL. These should be replaced by IS NULL or IS NOT NULL. Locks should use "with" and index references should be like this - WITH (INDEX(indx_name)).

Also be careful using -- to comment.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

quote:
Also be careful using -- to comment.

any issues with that?

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 8

It was another DBA in our company that did an Audit for us on 6.5 to 2000 upgrade. This is what he reported----

In SQL 6.5, the upgrade changes the -- by removing it. This allows that command to execute in the stored procedure.

All comments should be changed to */ instead to avoid any problems in the future.

Replace

-- this is a comment

with

/* this is a comment */

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

quote:
In SQL 6.5, the upgrade changes the -- by removing it. This allows that command to execute in the stored procedure.
All comments should be changed to */ instead to avoid any problems in the future.

how do you say?

Shot oneself in the foot.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

nmederich Old Hand Points: 347 More actions · Answer 10

Thanks for everyone's input. By the way I have experienced problems using /* */ for commenting (in stored procedures) when there is an embedded GO in the comments. Changing each comment line to begin with double dash -- solved that problem.

quote:
It was another DBA in our company that did an Audit for us on 6.5 to 2000 upgrade. This is what he reported----
In SQL 6.5, the upgrade changes the -- by removing it. This allows that command to execute in the stored procedure.
All comments should be changed to */ instead to avoid any problems in the future.
Replace
-- this is a comment
with
/* this is a comment */

swimszoots Newbie Points: 5 More actions · Answer 11

Has anyone written any code, in any language, that would translate a select statement from the old style join syntax to ansi standard join syntax?

Our software works with multiple database engines, at least one of which does not support ansi standard join syntax. We already have a parsing algorithm that translates from one type of DB to another, but automatically translating from the old style syntax would seem to be significantly more difficult.

Thanks,

-bob

rstone SSCertifiable Points: 6209 More actions · Answer 12

quote:
...the upgrade changes the -- by removing it....

This sounds like a serious bug to me. What's next, removing quote comment delimiters in VB upgrades.

I use the "--" syntax except when debugging. Doing so avoids nesting or batch problems with the the /* */ syntax.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 13

Just confirming some of the comments made.

http://www.microsoft.com/sql/techinfo/tips/development/July23.asp