Comparison operator *=
-
SQL select statement in a SQR program has a comparison operator that I have not used before and I cannot seem to find in any SQL reference book. The WHERE clause has several contitions where the comparison operator is *=. (example: A.FIELD1 *= B.FIELD1). Can someone let me know what this operator is and the condition it is looking for?
-
... TableA A LEFT JOIN TableB B on A.FIELD1 = B.FIELD1
* Noel -
This is the old JOIN syntax. You better forget about it and SQL Server. Read this http://www.microsoft.com/sql/techinfo/tips/development/July23.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The *= is the Non-ANSI way of specifing a LEFT OUTER JOIN. If the operator was =* it would be a RIGHT OUTER JOIN. You should convert it to the appropriate syntax as shown in Noels post above.
--------------------
Colt 45 - the original point and click interface
-
Good article reference, Frank (and congrats again on the MVP!) I did not know about precedence of when the WHERE clause gets evaluated between the two forms.
This begs a question of me, though. I recently wrote the following code:
select distinct fieldlist blahblahblah....
from EntityHistory en
join enLookupCodes lc1 --get state
on ad1.refstateid = lc1.id
join enEntityAddressTypes adt1 --filter address type
on ad1.id = adt1.EntityAddressID
and adt1.refAddressTypeid = 950 --mailind addr
join enEntityAddressTypes adt2
on ad2.id = adt2.EntityAddressid
and adt2.refAddressTypeid = 951 --physical addr
where en.fiscalyear = 2004
and en.ownerid is not null --select schools, not districts
and en.ctds in (select ctds from EdzOutOfStateEntities)
My question to you is, would putting effectively a Where clause on the Join be 'improper', would it be better to put it in the actual Where clause? Or perhaps this is more a matter of personal style?
The thing that broke me of using *= and =* was being unemployed for about 10 months and not using SQL Server during that time. When I started coding again, it was real easy to pick up the Join syntax. <g>
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Thanks. And the moment where SQL Server might not support this old syntax style might come sooner as one might think.
As for the WHERE clause. AFAIK, is this valid ANSI syntax. It is even described in SQL 2003, though I'm not sure if ANSI has adopted this standard (I think notm but might be wrong). So, I guess it will stay valid for some time. I'm used to the explicite JOIN syntax and when you see the execution plans SQL Server creates, both are identical. And actually I have no other argument (right now) as the one, that using the JOIN syntax is the preferred way of doing things.
I've seen Joe Celko posting here again today. Maybe he becomes aware of this thread and can explain first-hand.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply