By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,473 Members | 1,274 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,473 IT Pros & Developers. It's quick & easy.

*=

P: n/a
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

Dec 9 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

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
Dec 9 '05 #2

P: n/a
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
Dec 9 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.