Re: *=

Bruno Panetta (bpanetta@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: CONTAINS performance
    ... How can I predict the scalability of the simple query given that I don't ... but will measure the FTS with multiple clients issuing random FTS queries. ... B on A.bid=B.id left outer join ... the SQL Server query optimizer executes this query in the optimal manner ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Why is this Not Updatable?
    ... record source as the sql query I posted earlier called ... end and I know I have not done any relationships on the sql server or if this ... We have many customers that could be billed to the billing ... >> is stored in the BillTo table where the BillToID is the primary ...
    (microsoft.public.access.queries)
  • Re: how to improve performance of LEFT JOIN
    ... LEFT OUTER JOIN TableB ... I add the subquery to query every table before 'LEFT JOIN' ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: How to query with both JOIN and aliases in Access?
    ... v.empID LEFT OUTER JOIN FirstDayWorked FROM ... This query performs in SQL Server without any problem. ... LEFT OUTER JOIN (SELECT empID, ...
    (microsoft.public.access.queries)
  • Re: combining inner and outer joins
    ... the query that works for you in Sybase, and the output you're looking for. ... > I've recently switched from Sybase to SQL Server, ... > of an outer join clause. ...
    (microsoft.public.sqlserver.programming)