In our previous examples we queried only one table at a time. Typically data stored in the database will be in more than one table. What then? The join and subquery features give you the ability to query multiple tables. Keep in mind the more tables involved in a SELECT statement, the more complex the SQL.

The function of combining data from multiple tables is called joining. A join will match the data from two or more tables based on the values in columns you specify. All matches are combined, creating a resulting row, which is the concatenation of the columns from each table.

Looking at the EMPLOYEE table we can see that we have a column called DEPTNO. Suppose we had a table called DEPT, which carried attributes such as department number, department name, location of the department, manager number of the department and columns for budget figures such as expenses and revenue.

Here is the data stored in that table:

Now suppose we needed to know the office location for the employee named 'Chen'.

In reviewing the DEPT table above we note that department 10 is in 'Dallas' but the table does not include the employee name field.

By reviewing the EMPLOYEE table below we can see that 'Chen' works in department 10 but the location of that department is not contained within this table.

How do we get this information without doing two separate SELECT statements?

Since both the EMPLOYEE table and the DEPT table carry the column DEPTNO we can join the tables on that column and relate rows from the DEPT table with rows in the EMPLOYEE table. In doing this we are creating a virtual table which contains all the attributes for rows from the department table and the employee table where the DEPTNO is equal. This virtual table contains the following attributes: DEPTNO, DEPTNAME, LOC, MGR, EXP_BUDG, REV_BUDG, EMPNO, NAME, JOB, SALARY, COMM, and SEX.

In ORACLE we are able to join a multitude of tables together in this fashion. Although it may not be practical it is possible. Be aware that the more tables we join the more complicated the SQL becomes.

In order to find the location of the employee named Chen, we would use the following query.

AND EMPLOYEE.DEPTNO = DEPT.DEPTNO; ç join condition

The results table consists of one row that meets the specifications of the where clause. Since we selected only name and location, only those columns were returned. However, we can retrieve as many attributes from each table as we need.

Let's join all the rows of the EMPLOYEE table with the DEPT table selecting department number, department name, employee number, employee name, job, sex and salary and order the rows by employee number within department number. The query to retrieve this information is written below.

 SQL> SELECT EMPLOYEE.DEPTNO, DEPTNAME, EMPNO, NAME, JOB, SEX,

WHERE EMPLOYEE.DEPTNO = DEPT.DEPTNO ç join on DEPTNO

The results table should look like this.

Note something unusual about this query? Since we wanted to list DEPTNO as part of the output and this attribute resides within each table, we had to tell ORACLE which table (it doesn't matter which one) we wanted to use to extract DEPTNO. When you have an attribute which resides in more than one table if you do not specify the table in the SELECT statement you will get an ambiguity error.

To cut down on the amount of typing required in your queries you can use aliases in the SELECT and WHERE clauses. For example, if you wanted to use the abbreviation 'E' for the EMPLOYEE table in your query all you need do is tell ORACLE that EMPLOYEE will be reference by E in the FROM clause. The following query would result in the same output:

FROM EMPLOYEE E, DEPT ç alias table

WHERE E.DEPTNO = DEPT.DEPTNO ç join on DEPTNO

With SQL we are able to nest SELECT statements together in what are called subqueries. Using a subquery allows you to search the results table from another SELECT statement.

Going back to our previous example of retrieving the office location for Chen we could have used a subquery to obtain the same information.

 SQL> SELECT LOC ç main select query

(SELECT DEPTNO ç subquery

LOC

--------------- ç results table

Dallas

ORACLE processes this statement by first finding the department number for all the rows where the name is equal to 'Chen' and stores it in a virtual table which contains one column (DEPTNO). ORACLE then processes the next SELECT statement 'SELECT LOC FROM DEPT WHERE DEPTNO IN' by matching department numbers in the DEPT table to department numbers returned from the first SELECT statement. Whenever these are equal, the location is returned to the results table.

Suppose we want to find the employee name, employee number and department number for all employees who work in Houston, with the results in descending employee number order. One way to write this query is show below.

SQL> SELECT NAME, EMPNO, DEPTNO ç main select

(SELECT DEPTNO ç subquery

The results table will look like this.

The above query could have been formatted using the EXISTS operator. The EXISTS and NOT EXISTS operators return a value of true or false depending on the subquery that follows it. If the query returns at least one row which satisfies the conditions specified then the value of the query is true. The lack of any rows satisfying the specified conditions means the value of the operator is false. For example:

WHERE EXISTS ç EXISTS operator

 The results table is identical to the one received from the previous query.

Subqueries may also be used when working with data from a single table. For example using the EMPLOYEE table, suppose we want to retrieve the names and salaries for all employees who earn more than the average salary.

Using the group function AVG the SQL implementation is as follows:

SQL> SELECT NAME, SALARY ç main select

(SELECT AVG(SALARY) ç subquery

 The results table will look like this.

Recall that in our lesson on Group Functions,  we noted that column names may not be used with group functions in SELECT statements. For example, in order to find the name of the employee who earns the least salary, a subquery is needed. The query to retrieve this data is shown below.

The results table from this query is shown below.

NOTE: Many subquery statements can be converted to equivalent join statements unless they contain EXISTS or NOT EXISTS. Equivalently, many joins can be rewritten as subqueries.

ORACLE contains an extensive set of SQL commands. It is not the intention of this tutorial to cover all of these commands. Consult a book on ORACLE for further information.

For you to try:

1. Suppose you need to know the name, job, department name, and location of all female employees. Write a SQL query that will return this information. Order the list in alphabetical order by employee name.
2. Suppose you need to know the name, department name, and employee number of all managers. Write a SQL query that will retrieve this information. Order the list in alphabetical order by department name.
3. Suppose you need to know the name and department name of the employee who earns the highest salary. Write a SQL query to return this information.
4. Suppose you need to know the name, department name, and commission of all employees who were paid commission. Write a SQL query to retrieve this information. Order the list in alphabetical order by employee name.
5. Suppose you need to know the employee name and department name of all employees that work in a department that has at least 3 employees. Write a SQL query to retrieve this information. Order the list in alphabetical order first by department name, then by employee name.

Go here to check your answers.

Last revised 10/22/1997 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems