Posted by Erland Sommarskog on 12/09/05 21:55

Bruno Panetta (bpanetta@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]