Old join operator

Hi,
I have a question about the old join operators "*=" and "=*".
The BOL says they are left and right outer joins respectively.
I have an old sp using a cascading "*=" to join 3 tables.
But the result is quite strange: it is a cross product between the 1st and
2nd table with an outer join on the 3rd one...
Anyone knows what really does these old operators do ?
ThanksProabably the SP will be missing the join criteria for
first and second table in the where clause.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"fdudan" <fdudan@.discussions.microsoft.com> wrote in message
news:7952C6F6-5A0D-435F-8A1C-02E06E1DC265@.microsoft.com...
> Hi,
> I have a question about the old join operators "*=" and "=*".
> The BOL says they are left and right outer joins respectively.
> I have an old sp using a cascading "*=" to join 3 tables.
> But the result is quite strange: it is a cross product between the 1st and
> 2nd table with an outer join on the 3rd one...
> Anyone knows what really does these old operators do ?
> Thanks|||Replace *= and =* with outer joins. The results may vary if you need a WHERE
clause as well but the problem should be easier to identify and solve with
the new syntax. The old syntax may be dropped from future versions of SQL
Server so now is the time to fix it.
David Portas
SQL Server MVP
--|||> Replace *= and =* with outer joins.
Of couse, *= and =* are actually SQL89 syntax for outer joins. I meant that
you should replace them with the SQL92 syntax: LEFT JOIN ... ON / RIGHT JOIN
... ON :-)
David Portas
SQL Server MVP
--|||David,

> Of couse, *= and =* are actually SQL89 syntax for outer joins.
I don't have a copy of SQL-89, but I don't think that it defined OJ's at all
. I always thought that
*= was Sybase own invention, adapted only by Sybase...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:0A24AFDD-48AB-439B-AFC9-C42C37FC7A3A@.microsoft.com...
> Of couse, *= and =* are actually SQL89 syntax for outer joins. I meant tha
t
> you should replace them with the SQL92 syntax: LEFT JOIN ... ON / RIGHT JO
IN
> ... ON :-)
> --
> David Portas
> SQL Server MVP
> --
>|||It is Tibor - good memory... Then the Ansi standard began to include the
"new" syntax...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"fdudan" <fdudan@.discussions.microsoft.com> wrote in message
news:7952C6F6-5A0D-435F-8A1C-02E06E1DC265@.microsoft.com...
> Hi,
> I have a question about the old join operators "*=" and "=*".
> The BOL says they are left and right outer joins respectively.
> I have an old sp using a cascading "*=" to join 3 tables.
> But the result is quite strange: it is a cross product between the 1st and
> 2nd table with an outer join on the 3rd one...
> Anyone knows what really does these old operators do ?
> Thanks|||I think you may be right. I think of "*=" as SQL89 just because it predates
the SQL92 standard and I remember it from my early days on Sybase. But then
Oracle used the + notation after the table name, which is different again, s
o
I guess *= was Sybase specific.
David Portas
SQL Server MVP
--|||>> Anyone knows what really does these old operators do ? <<
Well, that depends on which release and which product.
Sybase had the *=, Oracle used the (+), INFORMIX had OUTER keyword in
the FROM table list, etc. all with different semantics. Centura (nee
Gupta) allowed you to configure the system with either the Oracle or
the Sybase semantics. Everyone had different rules about wheather a
table could be both an inner and outer reference. And nobody allowed
outer join on anything but equality.
The infixed OUTER JOIN operator came in SQL-92 and was inspired by an
article that Chris Date wrote. The other inixed join operators were
added because the definitions were easy and it gave a completeness to
the language.
Here is how OUTER JOINs work in SQL-92. Assume you are given:
Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z
and the outer join expression:
Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition
We call Table1 the "preserved table" and Table2 the "unpreserved table"
in the query. What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.
1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.
2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN
3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.
So let us execute this by hand:
Let @. = passed the first predicate
Let * = passed the second predicate
Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @.
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @.
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @.* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
--
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
--
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
--
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL
the final results:
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL
The basic rule is that every row in the preserved table is represented
in the results in at least one result row.
There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables
Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250
and let's do an extended equality outer join like this:
SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;
If I do the outer first, I get:
Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL
Then I apply the (qty < 200) predicate and get
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
Doing it in the opposite order
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL
Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;
.. or do it in the joining:
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;
Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-92.
For example to find the students who have taken Math 101 and might
have taken Math 102:
SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;|||It is very clearly not recommended to use the *= or =* structure. Inthe pas
t
it was inteneded as an outer join, I belive, but SQL Server 2000 has
difficulty interpreting them and may use a cross join iinsted of an
outerjoin. Since the results may not be the ones you expect, you should avoi
d
the use. If you look *= up in Books Online it will tell you specifically not
to use this construction.
"fdudan" wrote:

> Hi,
> I have a question about the old join operators "*=" and "=*".
> The BOL says they are left and right outer joins respectively.
> I have an old sp using a cascading "*=" to join 3 tables.
> But the result is quite strange: it is a cross product between the 1st and
> 2nd table with an outer join on the 3rd one...
> Anyone knows what really does these old operators do ?
> Thanks|||"HLGEM" <HLGEM@.discussions.microsoft.com> wrote in message
news:9B582295-6495-495C-A581-8B85D98A7CDC@.microsoft.com...
> It is very clearly not recommended to use the *= or =* structure. Inthe
> past
> it was inteneded as an outer join, I belive, but SQL Server 2000 has
> difficulty interpreting them and may use a cross join iinsted of an
> outerjoin. Since the results may not be the ones you expect, you should
> avoid
> the use. If you look *= up in Books Online it will tell you specifically
> not
> to use this construction.
>
and you won't be able to use the '*=' syntax in next version of sql server
(yukon) at all, unless you set the compatibility level to 80 or lower. can't
say i'm going to miss that one..
dean