join statement

when I am trying to optimize a proc, I saw one code which I don't know

select a.col1, b.col1

from tbl1 a, tbl2 b

where a.col2*=b.col2

what does this mean ( what will be the result set)

how can I write it in different way

Thanks

This is an old-style outer join which is actually deprecated in sql server 2005. Your code can be written this way

select
a.col1,
b.col2
from tbl1 a
left join tbl2 b ON a.col2 = b.col2

JOIN queries ?

Hi,

Can you give the equivalent query for the following Oracle query :

SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
AND so.pid (+) = 'SO'
AND so.cde (+) = 'RW'

In the above query a constant value is used in JOIN, so I am not aware of how to use the JOIN clause for the above constant value ( 'SO' , 'RW' )

Upto my knowledge about JOIN query equivalents :

Oracle query :-

select *from test1 a, test2 b where a.sno(+) = b.col

Equivalent SQL Server query :-

select *from test1 a JOIN test2 b on a.sno = b.col

Hope the above is correct. Here my doubt is, if in the above query, b.col is replaced by a constant value say 'abcd' , then what will be the table name used in right of the JOIN clause. Or any other equivalent for this is available ?

Eg :-

Oracle query :-

select *from test1 a, test2 b where a.sno(+) = 'abcd'

Thanks,
SamHi Sam,

the syntax used by Oracle with "(+)" in the WHERE-clause is equivalent to an OUTER JOIN in SQL Server and DB2.

The example you mentioned should be a LEFT OUTER JOIN, but I'm always a bit confused when using OUTER JOINS in Oracle, so I may be wrong :-)

You can lookup Oracle syntax at http://tahiti.oracle.com
Carsten|||Hi, OK, it is RIGHT JOIN, but my requirement is different, Please go through my posting once again. Thanks for your information.|||SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
AND so.pid (+) = 'SO'
AND so.cde (+) = 'RW'

SELECT col2, col3 FROM submission
Right Outer Join lkup so
On so.txt = LTRIM(RTRIM(col2)) AND so.pid = 'SO' AND so.cde = 'RW'

JOIN queries

Hi,

Can you give the equivalent query for the following Oracle query :

SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
AND so.pid (+) = 'SO'
AND so.cde (+) = 'RW'

In the above query a constant value is used in JOIN, so I am not aware of how to use the JOIN clause for the above constant value ( 'SO' , 'RW' )

Upto my knowledge about JOIN query equivalents :

Oracle query :-

select *from test1 a, test2 b where a.sno(+) = b.col

Equivalent SQL Server query :-

select *from test1 a RIGHT JOIN test2 b on a.sno = b.col

Hope the above is correct. Here my doubt is, if in the above query, b.col is replaced by a constant value say 'abcd' , then what will be the table name used in right of the JOIN clause. Or any other equivalent for this is available ?

Eg :-

Oracle query :-

select *from test1 a, test2 b where a.sno(+) = 'abcd'

Thanks,
SamSam,

The(+) is an outter join right?

How can you do an outter join to a literal?