Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to Outer Join, the Between Clause

572082May 11 2007 — edited May 17 2007
Dear SQL experts, this the Oracle own oracle example tables and example SQL.

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal

On no site on internet i could find the solution for point-1, though i was able to solve point-2.

1. if i want to see grade not applicable any employye that grade should appear with NULL ename and sal.
2. If i want to see employees lying outside of any grade

I modified the scott (EMP, SALGRADE) data as follows.

**************
Point-1 Plan
**************
TABLE: salgrade
GRADE LOSAL HISAL
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
6 6000 10000 <------- New row

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal(+) BETWEEN s.losal AND s.hisal

**************
Point-2 Plan
**************
TABLE: salgrade
EMPNO ENAME SAL
8000 MR_XY 20000 <------- New row

No combination of Outer/Full Join working.

What am i doing wrong, Is inline SQL the only solution for SQLs having Between Clause. I was able to do it with Inline-SQLs

Thank you.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 14 2007
Added on May 11 2007
10 comments
6,148 views