SQL SERVER – 2005 – Use Always Outer Join Clause instead of (*= and =*)

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.

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.

Reference : Pinal Dave (http://blog.SQLAuthority.com), SQL Joins

About these ads

21 thoughts on “SQL SERVER – 2005 – Use Always Outer Join Clause instead of (*= and =*)

  1. 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…

  2. 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?

  3. 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?

  4. 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.

  5. 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

  6. 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.

  7. 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

  8. 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

  9. 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!!

  10. 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

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s