Home » SQL & PL/SQL » SQL & PL/SQL » Where clause and a plus sign
Where clause and a plus sign [message #325933] Mon, 09 June 2008 10:59 Go to next message
SobeNo
Messages: 14
Registered: June 2008
Junior Member
Hello all,

I have a question which could be rather very trivial to most of us here but I did a web search on it but to no fruitation hence would like to ask the Gurus here.

I have a query that has a (+) sign in the where condition.. i know it is used to define an outer join if used with in a join statement. However i have it used which is not essentially a join statement, let me give an example --

select tble_id, column
from table
where coulumn (+) = 'String_Value';

What does this (+) sign mean in the above statement?

Further a count(*) with and without the (+) sign are different.

Any insight in this would be greatly appreciated,

Thanks in Advance
Re: Where clause and a plus sign [message #325940 is a reply to message #325933] Mon, 09 June 2008 11:30 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
check this link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#sthref3175

Regards

Raj
Re: Where clause and a plus sign [message #325958 is a reply to message #325940] Mon, 09 June 2008 13:21 Go to previous messageGo to next message
SobeNo
Messages: 14
Registered: June 2008
Junior Member
Raj,

Thank you for the link. It did add more to my knowledge about joins but was still not able to get the answers to my original questions. I have pasted the query again,

Quote:

select tble_id, column
from table
where coulumn (+) = 'String_Value';



The above where clause does not look to me as a join condition, yet has a (+) like the outer joins do. I don't know if the article you forwarded had explanation on this part and may be i wasn't able to make out of it all together.

Anyways, any additional insight to this would be much appreciated,

Thanks
Sobe

Re: Where clause and a plus sign [message #325959 is a reply to message #325958] Mon, 09 June 2008 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 59751
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This (+) is useless without it you get the same thing.

Regards
Michel
icon14.gif  Re: Where clause and a plus sign [message #325961 is a reply to message #325959] Mon, 09 June 2008 13:59 Go to previous messageGo to next message
SobeNo
Messages: 14
Registered: June 2008
Junior Member
Thank you Michel, I appreciate it ...
Re: Where clause and a plus sign [message #326156 is a reply to message #325933] Tue, 10 June 2008 06:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Further a count(*) with and without the (+) sign are different.

I'm afraid I don't believe you.

Could you post some evidence of this please.
Re: Where clause and a plus sign [message #326159 is a reply to message #325933] Tue, 10 June 2008 06:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Just in case you don't believe JRowbottom or Michel:
SQL> create table faq (col1 varchar2(10), col2 varchar2(10))
  2  /

Table created.

SQL> insert into faq (col1, col2) values ('My String', null);

1 row created.

SQL> select count(*)
  2  from   faq
  3  where  col2 = 'A String';

  COUNT(*)
----------
         0

SQL> select count(*)
  2  from   faq
  3  where  col2(+) = 'A String';

  COUNT(*)
----------
         0

SQL> drop table faq;

Table dropped.


Perhaps you oversimplified your example and left out a join to another table?
Re: Where clause and a plus sign [message #326192 is a reply to message #325933] Tue, 10 June 2008 09:45 Go to previous messageGo to next message
SobeNo
Messages: 14
Registered: June 2008
Junior Member
Quote:

I'm afraid I don't believe you.
Could you post some evidence of this please.



I realized I had been querying off a table that gets updated every instance. During the time I changed the condition in SQL editor, the number of rows would have changed. In all the different runs I have gotten different counts. So I believe you guys. Thanks for all the insight.

Sobe
Re: Where clause and a plus sign [message #326194 is a reply to message #325933] Tue, 10 June 2008 09:58 Go to previous messageGo to next message
SobeNo
Messages: 14
Registered: June 2008
Junior Member
Quote:
Perhaps you oversimplified your example and left out a join to another table?


Frank,

Does it mean that such a statement in "Where" clause if used in conjunction with some other join statement would signify a different kind of join and/or has advantages?

Sobe
Re: Where clause and a plus sign [message #326229 is a reply to message #326194] Tue, 10 June 2008 13:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As the link in Raj's first reply explains, (+) is used to define an outer join. Read that link and see if you understand.
Play around with the emp and dept tables to get the hang of it and see what happens. In the beginning outer joins are hard to grasp, but once you understand them, you have mastered an important skill.
Re: Where clause and a plus sign [message #326240 is a reply to message #326229] Tue, 10 June 2008 16:17 Go to previous messageGo to next message
SobeNo
Messages: 14
Registered: June 2008
Junior Member
Frank,

Thanks for the insight. I do know about outer joins not an expert though and obviously the link that Raj sent did add up to my understanding. However, since you said that i must have left out a join in my example and must've over simplified it with just keeping this:-
Quote:
column (+) = 'String_Value'


And as rest of the folks said that there is no difference in either of the following,
column (+) = 'String_Value'
column = 'String_Value'

My question is- would it have been that the above clause had been used to supplement one of the join statements in the query? Or did I miss the whole point altogether again? Embarassed

Sobe




Re: Where clause and a plus sign [message #326305 is a reply to message #326240] Wed, 11 June 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59751
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
(+) used with a constant can be useful along with an outer join (a condition between 2 tables).
For example, using the usual scott tables:
SQL> select d.deptno, d.dname, count(e.empno) cnt
  2  from dept d, emp e
  3  where e.deptno (+) = d.deptno
  4  group by d.deptno, d.dname
  5  order by d.deptno
  6  /
    DEPTNO DNAME                 CNT
---------- -------------- ----------
        10 ACCOUNTING              3
        20 RESEARCH                3
        30 SALES                   6
        40 OPERATIONS              0

4 rows selected.

Now add a condition on employees, for instance count all SMITH in department and see the difference with and without (+):
SQL> select d.deptno, d.dname, count(e.empno) cnt
  2  from dept d, emp e
  3  where e.deptno (+) = d.deptno
  4    and e.ename      = 'SMITH'
  5  group by d.deptno, d.dname
  6  order by d.deptno
  7  /
    DEPTNO DNAME                 CNT
---------- -------------- ----------
        20 RESEARCH                1

1 row selected.

SQL> select d.deptno, d.dname, count(e.empno) cnt
  2  from dept d, emp e
  3  where e.deptno (+) = d.deptno
  4    and e.ename  (+) = 'SMITH'
  5  group by d.deptno, d.dname
  6  order by d.deptno
  7  /
    DEPTNO DNAME                 CNT
---------- -------------- ----------
        10 ACCOUNTING              0
        20 RESEARCH                1
        30 SALES                   0
        40 OPERATIONS              0

4 rows selected.

Regards
Michel
Re: Where clause and a plus sign [message #326306 is a reply to message #326240] Wed, 11 June 2008 00:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Here's an example (I left out PKs and FKs for simplicity)
SQL> create table faq(id number, my_val varchar2(15));

Table created.

SQL> create table faq_detail (id number, faq_id number, detail_val varchar2(15));

Table created.

SQL> insert into faq values (1, 'One');

1 row created.

SQL> insert into faq values (2, 'Two');

1 row created.

SQL> insert into faq values (3, 'Three');

1 row created.

SQL> insert into faq_detail values (1, 1, 'OneOne');

1 row created.

SQL> insert into faq_detail values (1, 2, 'OneTwo');

1 row created.

SQL> insert into faq_detail values (2, 2, 'TwoTwo');

1 row created.

SQL> select *
  2  from   faq
  3  ,      faq_detail
  4  where  faq.id(+) = faq_detail.faq_id
  5  and    faq.my_val(+) = 'Three';

        ID MY_VAL                  ID     FAQ_ID DETAIL_VAL
---------- --------------- ---------- ---------- ---------------
                                    1          1 OneOne
                                    2          2 TwoTwo
                                    1          2 OneTwo

SQL> select *
  2  from   faq
  3  ,      faq_detail
  4  where  faq.id(+) = faq_detail.faq_id
  5  and    faq.my_val = 'Three';

no rows selected


See how the (+) in the join-condition with the string literal makes a difference now?
icon14.gif  Re: Where clause and a plus sign [message #326494 is a reply to message #325933] Wed, 11 June 2008 13:28 Go to previous messageGo to next message
SobeNo
Messages: 14
Registered: June 2008
Junior Member
Thank you Michel, Thank you Frank for taking out time to explain this through. I understand it very well now. Again thank you guys for all the time.

Sobe
Re: Where clause and a plus sign [message #326551 is a reply to message #326494] Wed, 11 June 2008 23:34 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

The new thing i come to learn from your discussion.. Smile

Thank you all...
Keep it on..


Regards..
Previous Topic: CHR(13)||CHR(10) at the end of the sql
Next Topic: Object type Casting and %type (merged)
Goto Forum:
  


Current Time: Sun Nov 23 13:18:46 CST 2014

Total time taken to generate the page: 0.08365 seconds