Home » Technology » Oracle Databases » Joining Tables and Using Sub-queries in SQL: UNION, EXISTS, Scalar, and Inline Views

Joining Tables and Using Sub-queries in SQL: UNION, EXISTS, Scalar, and Inline Views

© Lewis R Cunningham

Here is an example of joining multiple tables using the sample schema SCOTT, which is supplied with the Oracle database.

SELECT emp.ename, dept.dname
FROM emp JOIN dept
ON emp.deptno = dept.deptno
/

This returns the employees' names and departments. There is a table called SALGRADE in the SCOTT schema that contains payband and grade information. How would you include this information in your query?

SELECT emp.ename, dept.dname, sg.grade
FROM emp
JOIN dept ON emp.deptno = dept.deptno
JOIN salgrade sg ON emp.sal BETWEEN sg.losal AND sg.hisal
/

Now we've introduced a new table, SALGRADE (aliased as SG) and column, grade, by joining to the SALGRADE table. In this case, they are joined using the BETWEEN operator. The paybands in the SALGRADE table use a range of salary to designate a grade. The new join says to join the salgrade table to the emp table where the emp sal value is greater than the salgrade minimum (losal) but less than the salgrade maximum (hisal).

Here is a modification to return only those employees whose salary is exactly on a payband.

SELECT emp.ename, dept.dname, sg.grade
FROM emp
JOIN dept ON emp.deptno = dept.deptno
JOIN salgrade sg ON (emp.sal = sg.losal OR emp.sal = sg.hisal)
/

Definition of a Sub-query in a SQL Join

In the context of a join, a sub-query will include or restrict value from one table while joining to another table. In the example above, how would you get all of the employees except those that are exactly on a payband?

SELECT emp.ename, dept.dname, sg.grade
FROM emp
JOIN dept ON emp.deptno = dept.deptno
JOIN salgrade sg
  ON ((emp.sal BETWEEN sg.losal AND sg.hisal)
    AND emp.sal NOT IN (
      SELECT losal
        FROM salgrade
      UNION
      SELECT hisal
        FROM salgrade ))
/

The AND emp.sal NOT IN clause tells the SQL engine to not fetch rows that meet the criteria specified in the SELECT statement. In this case, we are selecting the losal and hisal values as a single column and introducing the UNION clause. UNION allows you to combine two queries into a single result set. You can run:

      SELECT losal
        FROM salgrade

and 

      SELECT hisal
        FROM salgrade

by themselves. Each query will return five rows with a single column. By combining them with UNION, you get back ten rows with a single column.