3

I am stuck on trying to find the meaning of a plus sign in a where clause. Anyone have any ideas on this one? Been stuck for a bit on it. The query itself is pretty simple and work similarly with, or with without the plus sign. I'd like to remove it unless it's there for a reason.

SELECT userID from tblUser WHERE + userName = SYSTEM_USER

Added note: This is in SQL Server 2008 not Oracle, nor did it come from and Oracle migration... As mentioned below there is an older join notation for Oracle that uses the + generally postfixed to some of the criteria.

RyanInAK
  • 43
  • 1
  • 5
  • The final determination was that it’s either a fat finger or coding remnant that had no operative effect on the query. Hence was never found in any debugging processes. Thank You All Greatly For Your Help And Expertise! – RyanInAK Apr 29 '14 at 17:53

2 Answers2

6

The unary + operator is simply a no op. This is explained in the documentation for this operator, which is here:

Although a unary plus can appear before any numeric expression, it performs no operation on the value returned from the expression. Specifically, it will not return the positive value of a negative expression. To return positive value of a negative expression, use the ABS function.

I actually believe this remark is a wee little bit misleading. I think the unary plus operator will convert a string argument to a number. When applied to a constant string filled with digits, this could actually be beneficial as a way of encouraging the compiler to use an index on a numeric field.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I think you hit it on the head... I'm starting to feel this was a fat finger or leftover of a concatenation and since it parsed and executed just fine. The execution is the same regardless of whether the plus sign was there or not. – RyanInAK Apr 29 '14 at 17:51
0

It looks like the plus operator in the where clause is used for left or right outer joins.

You don't need it in your case, but you can read up on them here.

The reason your query works the same either way is because data is only coming from a single table. The join is superfluous.

A quick search also lead me to this answer, which states using the + method for joins is not recommended.

Update

Since you're using Microsoft SQL Server 2008, this is my best guess:

The '+' operator is used for string concatenation.

Community
  • 1
  • 1
Bryce
  • 93
  • 8
  • I think you are referring to += or =+. those are the old school left/right join methods. – attila Apr 29 '14 at 00:00
  • @attila You're right. In this case I may be incorrect. Normally the + operator is just reserved for addition, so it may be ignoring it all together. – Bryce Apr 29 '14 at 00:11
  • This is in SQL Server 2008. I found what you where you were refereing to for older Oracle syntax but nothing for SQL server. The Oracle syntax is also different than the syntax presented here for the older JOIN notation. – RyanInAK Apr 29 '14 at 00:13
  • I don't have much experience with SQL Server 2008, but from what I can tell '+' is used for string concatenation. – Bryce Apr 29 '14 at 00:19