Yesterday I wrote about how SQL Server 2005 does not support named pipes. Today, my friend called me asking some of his query does not work. I asked him to send me the queries. I asked him to send me query. I noticed in his queries something, I have never practiced before and I never had any issue therefore. The problem was old join syntax in place of the outer join clause.
Instead of using LEFT OUTER JOIN clause he was using *= and similarly instead of using RIGHT OUTER JOIN clause he was using =*. Once I replaced did necessary modification, queries run just fine.
I wish I can give you example of *= or =*, however, I had decided to not to add any more SQL Server 2000 code to this blog few months ago.
How to Rewrite *= and =* With an Outer Join Clause
The old syntax put the join condition in the WHERE clause, with a star on the side of the table whose rows you wanted to keep. Under compatibility level 90, the SQL Server 2005 default, it raises an error, and newer versions do not support it at all.
To rewrite a query, follow three steps:
- Replace
a.col *= b.colwithFROM a LEFT OUTER JOIN b ON a.col = b.col. - Replace
a.col =* b.colwith aRIGHT OUTER JOIN, or swap the tables and useLEFT, which many people find easier to read. - Look at every other condition on the optional table, the one that may have no matching row.
That last step is where most bugs hide. If you filter the optional table in the WHERE clause, for example WHERE b.Status = 'Active', rows with no match have NULL in that column and get removed. Your outer join quietly turns into an inner join. Put that filter in the ON part instead, and the unmatched rows stay.
After the rewrite, compare the results of the old query, run on a test copy under compatibility level 80, with the new one. Check the row counts and look at a few rows that have no match on the other side.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
20 Comments. Leave new
Hi this good website to assistance sql
This Web site is quite simple..Every one can easily gain knowledge about SQL.
hi pinal i am using sql server 2005 express edition and i have two tables having totally different coloumn names and no primary key or foreign key in any of the coloumns of both the table so how can i join both the tables. plz help me its urgent plz…
As I know Higher Version of System always support the Lower versions property. Now In sql server 2k, we use =* or *= , but that is not possible in sql server 2005.
Then we have so many programs that runs on 2000 and Now we are upgrading the server into 2005..
what will be happen?
Hi, This Website is veru useful both for beginners and professionals
can you send me a book for sql server 2005
Pinal Dave,
I’m having trouble with an outer join query using SQL Server 2005.
SELECT sum(a.clicks) as clicks, l.linksid, h.location, s.subcategory
FROM aggregatelinkcounts as a
LEFT OUTER JOIN links as l ON a.linkID = l.linksID
LEFT OUTER JOIN subcategories as s ON s.id = l.subcategoryid
LEFT OUTER JOIN locations as h ON h.locationsid = l.locationsid
WHERE l.customersid = 44
AND a.date >= ‘2007/1/1’
AND a.date <= ‘2008/1/1’
GROUP BY l.linksID, s.subcategory, h.location
ORDER BY s.subcategory, h.location
The table Links has more occurrences of linkID than aggregatelinkcounts and so ‘clicks’ should have zeros showing up on those result rows with no inner join.
I understand that SQL 2005 doesn’t do this correctly. Is there a tweak or something I can do to make this query work correctly?
Very nice blog. Keep up the good work Pinal :)
IThe outer join syntax is excessively verbose and obfuscates the line between the ‘From’ clause as the ‘Where’ clause. I am hard pressed to believe that the developers at Microsoft could not support both syntaxes. They should restore the *=/=* syntax.
Hi Michael,
We’ve came across the similiar situations and we’re able to fix it. Please try this…
SELECT sum(a.clicks) as clicks, j.linksid, j.location, j.subcategory
FROM aggregatelinkcounts as a
LEFT OUTER JOIN
(select l.linksid, l.sustomersid, s.subcategory, h.location
from l
LEFT OUTER JOIN subcategories as s ON l.subcategoryid = s.id
LEFT OUTER JOIN locations as h ON l.locationsid = h.locationsid ) as j
ON a.linkID = j.linksid
WHERE j.customersid = 44
AND a.date >= ‘2007/1/1′
AND a.date <= ‘2008/1/1′
GROUP BY j.linksID, j.subcategory, j.location
ORDER BY j.subcategory, j.location
Good luck.
Jackie
Hi Pinal,
right now we are migrating out application from sql server 2000 to sql server 2005. we got Outer Join (*= and =*) issues and we modified those. Is there any issues other than this when migrating from from sql server 2000 to sql server 2005?
Thanks in advance.
can u send me a book for sql server 2005
I’m having trouble with an outer join query using SQL Server 2005.
select distinct pm.product_name,ps.productid,sum(ps.price)price from product_master pm
left outer join productsales ps on ps.productid=pm.product_id
left outer join salesdetail sd on sd.id=ps.salesid
where pm.type=’product’ and DATEPART(YEAR, sd.deliverydate) = ‘2009’
and DATEPART(Month, sd.deliverydate) = ‘1’
group by pm.product_name,ps.productid
I am not getting all product names
please help me
select distinct pm.product_name,ps.productid,sum(ps.price)price from product_master pm
left outer join productsales ps on ps.productid=pm.product_id
left outer join salesdetail sd on sd.id=ps.salesid
where pm.type=’product’ and DATEPART(YEAR, sd.deliverydate) = ‘2009’
and DATEPART(Month, sd.deliverydate) = ‘1’
group by pm.product_name,ps.productid
I am not getting all productname
@Pradnya
If you remove the WHERE clause, do you get all of them?
Hi Pinal,
Is there some free tool available on the net that will convert the syntax of *= & =* queries to LEFT & RIGHT OUTER JOIN?
Actually in my Project there are many such occurences where I will have to convert them manually. It will consume my development and testing efforts.Please suggest me if any such tool is available.
Thanks in Advance!!
Very Constructive Article Sir….
Hi Pinal,
Yesterday I went to an Interview, Interviewer has asked me a strange question where I couldn’t give him perfect answer. Question is here
1. Why do we need to use Left and Right Outer joins? Though you can swap tables name while using any one outer join.
Can you please give me your answer
thanks
Sandeep
Hi , This website is good guidence for SQL Learners.
Please send me sql 2005 ebook in pdf format