*=

[Bruno Panetta]

What is the meaning of *= in SQL?
For example, what is the difference between the query

select c.customernumber, o.amount , i.[description]
from customers c, orders o, items i
where c.customernumber = o.customernumber
and o.itemnumber = i.itemnumber

and

select c.customernumber, o.amount , i.[description]
from customers c, orders o, items i
where c.customernumber *= o.customernumber
and o.itemnumber *= i.itemnumber

The second one gives an error: "Query contains an outer-join request
that is not permitted", the first one runs ok.

Thanks,
Bruno

[Marek]

Uzytkownik "Bruno Panetta" <bp******@gmail.com> napisal w wiadomosci
news:11*********************@g43g2000cwa.googlegro ups.com...

What is the meaning of *= in SQL?
For example, what is the difference between the query

select c.customernumber, o.amount , i.[description]
from customers c, orders o, items i
where c.customernumber = o.customernumber
and o.itemnumber = i.itemnumber

and

select c.customernumber, o.amount , i.[description]
from customers c, orders o, items i
where c.customernumber *= o.customernumber
and o.itemnumber *= i.itemnumber

*= is old fashioned "left outer join" clause.

MP

[Erland Sommarskog]

Bruno Panetta (bp******@gmail.com) writes:

What is the meaning of *= in SQL?
For example, what is the difference between the query

select c.customernumber, o.amount , i.[description]
from customers c, orders o, items i
where c.customernumber = o.customernumber
and o.itemnumber = i.itemnumber

and

select c.customernumber, o.amount , i.[description]
from customers c, orders o, items i
where c.customernumber *= o.customernumber
and o.itemnumber *= i.itemnumber

The second one gives an error: "Query contains an outer-join request
that is not permitted", the first one runs ok.

As Marek said *= is an old syntax for outer join. When you say:

SELECT ...
FROM A, B
WHERE A.col = B.col

And there are 10 rows A, 5 rows in B of which all has a row in A that
matches B.col, the query returns 5 rows. If you instead say:

SELECT ...
FROM A, B
WHERE A.col *= B.col

You will get all 10 rows in A, and for the columns from B will have NULL
where there is no matching row there.

This syntax is old and deprecated, and you should not use it. There are
restrictions and all sorts of oddities. The proper syntax for writing
an outer join is:

SELECT ...
FROM A
LEFT JOIN B ON A.col = B.col
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx