Oracle Is there another way to get the answer like using a JOIN?

Question

Display the employee ID, LAST NAME, JOB_ID, Manager id for those employees who are in either the Sales or the Research department and a salary grade of C.

The answer was:

select empno, ename, job, mgr
 from emp
where deptno IN (select deptno
              from dept
              where dname IN ('SALES', 'RESEARCH'))
and sal between(select losal from salgrade
              where grade=3)
and (select hisal
    from salgrade where
      grade=3);

Table EMP has columns EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO.
Table SALGRADE has columns Grade, LoSal, HiSal.
Table DEPT has columns DEPTNO, DNAME, LOC.

But I was wondering if there was a way to join these tables and display the answer because my first thought would be trying to INNER join these tables together.

I tried doing something like this:

select e.empno,e.ename,e.job,e.mgr
from emp e
join dept d on e.deptno = d.deptno
join salgrade s on e.sal between (
  select losal
  from salgrade
  where grade =3) 
and (
  select hisal
  from salgrade
  where grade=3)

but couldn't find a way to include DNAME and I don't think the question asked for salary to be between losal and hisal.

APC APC · Accepted Answer · 2017-04-14T08:37:30

" I don't think the question asked for salary to be between losal and hisal."

The question states the salary needs to match a specific grade.

" a salary grade of C.

Of course SALGRADE in the classic SCOTT/TIGER schema has a numeric grade:

SQL> select * from salgrade;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

SQL>

The sloppiness of the question fits the bizarre nature of the answer. Anyway, as we can see, the grades are allocated to salary bands. So to find the employees in a particular grade we need to match their salary to the upper and lower bounds of that grade, losal and hisal.

Here is one solution:

SQL> select empno, ename, job, mgr
  2  from emp
  3       join dept
  4          on emp.deptno = dept.deptno
  5       join ( select * from salgrade
  6             where grade = 3 ) sg3
  7                     on emp.sal between sg3.losal and sg3.hisal
  8  where dept.dname IN ('SALES', 'RESEARCH')
  9  /

     EMPNO ENAME      JOB              MGR
---------- ---------- --------- ----------
      7499 ALLEN      SALESMAN        7698
      7844 TURNER     SALESMAN        7698

SQL>

I prefer to keep join conditions separate from filters. There are a couple of ways of doing this, and the query demonstrates both:

Filter the table in an inline view
Filter the table in the main query's WHERE clause

The syntax allows us to include non-joining conditions in the on clause, but this can have side-effects. It's better to be clarity between joins and filters, which separation is the main point of the ANSI 92 syntax.

Oracle Is there another way to get the answer like using a JOIN?

2 Answers