Oracle and SQL Server select (+) equivalent

Question

In Oracle I have:

SELECT t2.Regkood, t2.naitatel, t2.naitafaks, t3.lahtiolekuaeg, t1.*
FROM table1 t1, table2 t2, table3 t3
WHERE t1.client = t2.client AND t1.client = t3.client(+) AND t1.client = 414246

How do I get the same in SQL Server?

thanks

You should not use `(+)` in Oracle. Use standard `JOIN` syntax. — , Jun 12 '12 at 10:07

Romil Kumar Jain · Accepted Answer · 2019-05-21T10:30:31.363

8

SELECT t2.Regkood, 
       t2.naitatel, 
       t2.naitafaks, 
       t3.lahtiolekuaeg, 
       t1.* 
FROM   table1 t1 
       INNER JOIN table2 t2 
               ON t1.client = t2.client 
       LEFT JOIN table3 t3 
               ON t1.client = t3.client 
WHERE  t1.client = 414246

Some samples to understand joins:

LEFT OUTER JOIN in ORACLE

SELECT *
FROM A, B
WHERE A.column = B.column(+)

RIGHT OUTER JOIN IN ORACLE

SELECT *
FROM A, B
WHERE A.column(+)=B.column

edited May 21 '19 at 10:30

answered Jun 12 '12 at 10:06

Romil Kumar Jain

20,239
9
63
92

@Romil : Just out of interest can you please explain me what does + symbol stands for in Oracle ? – praveen Jun 12 '12 at 10:08
In previous version of oracle (we use +) and sql server (we use *) to denote left or right join. – Romil Kumar Jain Jun 12 '12 at 10:10
1

Your two Oracle joins are equivalent (`A LEFT JOIN B`). If instead you want to write `A RIGHT JOIN B` you would write something like this: `SELECT * FROM A,B WHERE A.column (+) = B.column` – Vincent Malgrat Jun 12 '12 at 11:21
I am afraid I don't see such thing on my page :| – babboon Jun 12 '12 at 12:21

score 0 · Answer 2 · answered Oct 04 '22 at 14:21

Actually to me the question looks like a concatenate question which in Oracle is used as two pipes side by side:

Oracle: select FirstName||' '||LastName Returns: John Doe (if FirstName = John and LastName = Doe)

is the same as

MSSQL: select FirstName+' '+LastName Returns: John Doe (if FirstName = John and LastName = Doe)

Oracle and SQL Server select (+) equivalent

2 Answers2

Linked

Related