outher joins formats

[raulgz]

Hi

I work ith sql server 2000 and i need know the diferent
of joins in format not ansi ( with * ) and joins in format
ansi ( with 'outher join on' ).
Two format work equal ???
What is de correct format ???
Thank you.

R.

[Simon Hayes]

ra****@ozu.es (raulgz) wrote in message news:<9b**************************@posting.google. com>...

Hi

I work ith sql server 2000 and i need know the diferent
of joins in format not ansi ( with * ) and joins in format
ansi ( with 'outher join on' ).
Two format work equal ???
What is de correct format ???
Thank you.

R.

You should always use the ANSI syntax, because the 'old style' joins
are unclear, and some queries cannot be written at all with them. In
addition, Microsoft may stop supporting the old syntax in a future
version of MSSQL.

In an outer join, you need to separate the join condition (in the JOIN
clause) from the filter conditions (in the WHERE clause) to make sure
you get consistent results. In the old style join, all the conditions
are together in the WHERE clause, so the database engine has to
'guess' at what you meant, and the results may not be what you want.

This KB article shows the difference:

http://support.microsoft.com/default...&Product=sql2k

Simon

[Andy S.]

Read more about this by downloading SQL books online free from
http://www.microsoft.com/sql

INNER JOIN FORMATS (only identical rows)

select *
from tablea, tableb
where tablea.id = tableb.id

select *
from tablea inner join tableb
on tablea.id = tableb.id
RIGHT OUTER JOIN FORMATS (all rows on right, shows null on left where no
match)

select *
from tablea, tableb
where tablea.id =* tableb.id

SELECT *
from tablea right outer join tableb
on tablea.id = tableb.id

LEFT OUTER JOIN FORMATS (all rows on left, shows null on right where no
match)

select *
from tablea, tableb
where tablea.id *= tableb.id

SELECT *
from tablea left outer join tableb
on tablea.id = tableb.id

FULL OUTER JOIN FORMATS ( shows null where no match)

Select *
from tablea, tableb
where tablea.id FULL OUTER JOIN tableb.id

Select *
from tablea, tableb
where tablea.id FULL JOIN tableb.id

CROSS JOINS (cross product, all possible combinations)

select *
from tablea, tableb

select *
from tablea cross join tableb

****************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
an********@NOMORESPAM.yahoo.com

Please remove NOMORESPAM before replying.

This posting is provided "as is" with
no warranties and confers no rights.

****************************************

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!