Plus sign in the select clause of an SQL

Question

I am working on an old SQL written for Oracle. There is a part of the select clause that I do not understand, even after googling and checking the questions related to the + sign with the key words Oracle and SQL.

I have checked the answer for a similar question for the where clause here and I found an Oracle docs for the join as well, but it does not make sense for the select clause.

Given an SQL below what is the purpose of the plus sign.

select '01,'|| 
lpad( 
   (select nvl(count(*), 0) from table1)
   +
   (select nvl(count(*), 0) from table2)
   +
   (select nvl(count(*), 0) from table3)
,9,'0')
from DUAL

The sample output

01,000011111

From a sample sql output, it seems that it is for concatenating a result, but I want to be sure.

Gordon Linoff · Accepted Answer · 2019-05-29T21:31:29.723

2

The + is not for string concatenation. It is for addition. Plus, count() does not return NULL values so nvl() is not needed.

This should be equivalent:

select ('01,' || 
        lpad( ((select count(*) from table1) +
               (select count(*) from table2) +
               (select count(*) from table3)
              ), 9, '0'
            )
        )
from DUAL

edited May 29 '19 at 21:31

answered May 29 '19 at 15:26

Gordon Linoff

1,242,037
58
646
786

Could you also please let me know if this + operator in the select clause oracle specific? – Rov May 29 '19 at 15:34
@Rovshen: the `+` operator is the standard operator in SQL to add numbers. Why do you think that it would be something "special". What operator would you expect to be responsible for adding numbers? – May 29 '19 at 16:42
Good point, I haven't had a need to do arithmetic in a select statement. Thank you for confirming! – Rov May 29 '19 at 18:03
I think he’s confused with the non-ISO outer join notation used optionally by Oracle, which also uses the operator + – James May 29 '19 at 18:40

score 1 · Answer 2 · answered May 29 '19 at 18:20

1

Rovshen, I agree with Gordon. The code is concatenating the '01,' to the formatted sum of the counts from three tables. The LPAD is zero filling the summed number value to 9 digits. "||" is the concatenation symbol in Oracle though there is also a concat function. It is rarely used.

answered May 29 '19 at 18:20

Mark D Powell

184
2

Thank you Mark, thanks for explaining the rest of the sql, I had to look up LPAD. – Rov May 29 '19 at 20:09

Plus sign in the select clause of an SQL

2 Answers2