How to Outer Join, the Between Clause
572082May 11 2007 — edited May 17 2007Dear 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.