| Where clause and a plus sign [message #325933] |
Mon, 09 June 2008 10:59  |
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 #325958 is a reply to message #325940] |
Mon, 09 June 2008 13:21  |
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 #326159 is a reply to message #325933] |
Tue, 10 June 2008 06:55 |
Frank
Messages: 7901
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 |
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 |
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 |
Frank
Messages: 7901
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 #326305 is a reply to message #326240] |
Wed, 11 June 2008 00:51 |
 |
Michel Cadot
Messages: 68647
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 |
Frank
Messages: 7901
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?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
