Why use "=+" and "=*" in "WHERE " clause?

Question

I have issue related database connection in java class,i don't know why use this "=+" and "=*" in "WHERE " clause.

Here's an example:

String where = null;
if (isOracleConnectionCache()) {
    where = "ValidInfo.InfoCode = FolderInfo.InfoCode AND ValidInfoGroup.InfoGroup =+ ValidInfo.InfoGroup AND FolderInfo.FolderRSN = ?";
} else {
    where = "ValidInfo.InfoCode = FolderInfo.InfoCode AND ValidInfoGroup.InfoGroup =* ValidInfo.InfoGroup AND FolderInfo.FolderRSN = ?";
}

Can anyone tell me ?

I have three question :

(1) What do "*" and "+" signs denote ?

(2) how do these =+ and =* work in the WHERE clause ?

(3)how it is compare with two table ?

possible duplicate of [SQL Server \*= Operator?](http://stackoverflow.com/questions/983862/sql-server-operator) — Frank Schmitt, Nov 12 '13 at 13:59
@FrankSchmitt - But it doesn't cover the `=+` operator. Though I strongly suspect that this isn't a real operator anyway. And that `x =+ y` is just being treated as `x = (+y)`. — Martin Smith, Nov 12 '13 at 14:13
Assuming `=+` is supposed to create an outer join in Oracle (which it doesn't) both versions should be removed and replaced with the standard ANSI `LEFT JOIN` syntax. Oracle and Microsoft highly recommend that as well. Microsoft deprecated the `=*` operator whereas Oracle only "recommends" to use the new syntax. — , Nov 12 '13 at 15:20

score 6 · Accepted Answer · answered Nov 12 '13 at 14:52

So, as already explained by others, the =* operator in SQL Server indicates an outer join.

However, in Oracle =+ is not an operator at all. It appears that ValidInfoGroup.InfoGroup =+ ValidInfo.InfoGroup is actually parsed as ValidInfoGroup.InfoGroup = (+ ValidInfo.InfoGroup), where + is the unary identity operator.

Since this code appears to be an attempt to write an outer join depending on which database is being used, it is incorrect when used in Oracle - it is actually doing a normal join. The proper way to write this condition in Oracle's custom syntax would be ValidInfoGroup.InfoGroup = ValidInfo.InfoGroup (+).

It would perhaps be better to use ANSI SQL join syntax to indicate the outer join, which I believe would remove the need to test which database is being used.

Thanks to Martin and Nicholas for helpful comments on the other answers.

score 2 · Answer 2 · edited Nov 12 '13 at 15:17

2

=* does OUTER JOIN in SQL Server

UPDATE

(+) = does OUTER JOIN in Oracle

I dont know what =+ does sorry for the confusion

edited Nov 12 '13 at 15:17

answered Nov 12 '13 at 13:39

mosaad

2,276
5
27
49

1

A brief experiment in Oracle seems to indicate that this is incorrect. Can you give a documentation reference? – Dave Costa Nov 12 '13 at 13:43
1

`=*` doesn't do `OUTER JOIN` in *standard* SQL. It is deprecated [right outer join](http://stackoverflow.com/a/2541021/73226) syntax in SQL Server. – Martin Smith Nov 12 '13 at 13:56
1

@DaveCosta (+) is indeed (outdated) Oracle syntax for an outer join, see http://docs.oracle.com/html/E26088_01/queries006.htm – Frank Schmitt Nov 12 '13 at 14:01
@FrankSchmitt - But in parentheses. The code in the question is `ValidInfoGroup.InfoGroup =+ ValidInfo.InfoGroup` – Martin Smith Nov 12 '13 at 14:02
@MartinSmith Sure, but as far as I understand it, DaveCosta was asking about the (+) operator mentioned in the answer, not about the =+ in the original question. – Frank Schmitt Nov 12 '13 at 14:04
1

@FrankSchmitt - No. Dave's comment was left before this was edited when the answer asserted `"=+" does OUTER JOIN in Oracle` – Martin Smith Nov 12 '13 at 14:07
@MartinSmith You're right, I hadn't noticed that the post had been edited. Sorry. – Frank Schmitt Nov 12 '13 at 14:10

score 0 · Answer 3 · answered Nov 12 '13 at 13:50

0

This is nothing but Outer Join in Oracle and MS-SQL Let us suppose i one table record is deleted and corresponding value is NULL so when You will compare with == from my understand it will create issue so we can use Outer Join like you mentioned in Your question.

answered Nov 12 '13 at 13:50

Subodh Joshi

12,717
29
108
202

I've tried doing some joins in Oracle using `=+` as an operator. It does not function as an outer join in my attempts. Can you provide an example or a link to documentation explaining the use of this operator? It seems to be a valid operator in Oracle SQL but so far I don't see how it is different from a normal `=` operator. – Dave Costa Nov 12 '13 at 13:57
1

@DaveCosta - It could just be `=` followed by unary `+` then? – Martin Smith Nov 12 '13 at 14:00
I am using in my application same as mentioned above – Subodh Joshi Nov 12 '13 at 14:04
2

`This is nothing but Outer Join in Oracle` No, `=+` operator is not a valid Oracle operator and has nothing to do with outer join. Indication of outer join(not ANSI SQL) in Oracle would be column name followed by `(+)` operator. Conditions `col =+ col` or `col =- col` will work, simply because plus or minus sign in those conditions are treated as unary arithmetic operators and condition `col =* col` will fail. – Nick Krasnov Nov 12 '13 at 14:25
`==` is invalid in SQL. It's **not** the equals operator. – Nov 14 '13 at 17:13

Why use "=+" and "=*" in "WHERE " clause?

3 Answers3