SQL Subqueries
This section discusses getting data based on unknown information (subqueries). Subqueries are useful when the condition of one query is based on information in another table. The syntax is as follows: SELECT column[s] FROM table[s] [(SELECT [column[s] FROM table[s] [WHERE...][GROUP BY ...] WHERE column|constant|expression operator (SELECT [column[s]] FROM table[s] [WHERE ...][GROUP BY ...]). You cannot use an ORDER BY in a subquery. The subquery is supplying row/column information to the outer query so there would be no reason to return rows in any particular order. Notice that you can have a subquery as part of the FROM clause.
Subqueries can be nested, or a subquery can contain a subquery. There is no technical limit to the depth of this type of query.
The subquery or “inner” query executes a single time before the main “outer” query executes. The result set from the inner query is then used by the outer query.
A query can contain multiple subqueries in that each part of a WHERE clause can contain a subquery. The HAVING clause may have a subquery as well.
There are four basic types of subqueries:
Those that return a single row
Those that return multiple rows
Those that return multiple columns in a single row
Those that return multiple columns in multiple rows
The single-row subquery has a subquery that only returns a single row. The comparison operators that test for the returned result are: = (equal), > (greater than), >= (greater than or equal to), < (less than), <= (less than or equal to), or <> (not equal). The subquery always goes on the right side of the comparison operator and is always enclosed in parentheses. Figure 7.25 shows a query that returns all the employees and their salaries that are above the average salary.
Figure 7.25. Single-row subquery example.
Figure 7.26 shows what happens when a single-row comparison operator is being used on a subquery that returns more than one row.
Figure 7.26. Single-row subquery error example.
Subqueries are useful when it is desired to list some columns and use a GROUP BY function. Figure 7.27 shows a query that will find the last employee hired. Notice that the first query failed, but when the WHERE clause is fitted with a subquery, the query returns the desired results.
Figure 7.27. Group function subquery example.
The multiple-row subquery operators are IN (equal to any value in the result set), ANY (compared to each value returned by the subquery), and ALL (compared to every value returned by the subquery). Multiple-row subqueries can return one or many rows. They are called multiple-row subqueries because they have the ability to handle more than one row returned by the subquery. Figure 7.28 shows a SQL statement that will find the highest paid employees of each department (useful for budget cuts). Notice that there are two individuals for DEPT 20. This is because both of these folks make the maximum salary for the department. Also notice that eliminating the job PRESIDENT from the output is in the subquery. Because the PRESIDENT is the highest paid person in DEPT 10, if this were in the outer query's WHERE clause, we would not have gotten a row for DEPT 10.
Figure 7.28. Multiple-row subquery example.
Sometimes it is necessary to compare more than one column from the results of a subquery. Multiple-column subqueries return more than one column. The rules here are that the same number of columns specified in the WHERE clause must be returned by the subquery. There are two types of multiple-column subqueries: pairwise (where there are two or more columns returned by the same subquery) and nonpairwise where there are multiple subqueries each returning a single row. Figure 7.29 shows both kinds of subqueries in action as well as the different result sets that are produced. The big difference between pairwise and nonpairwise is that pairwise will ensure that the combined columns are from the same row, where the nonpairwise will return the rows that meet the column criteria but not necessarily where the column combination appears in the same row.
Figure 7.29. Multi-column subquery examples.
Oracle9i supports a subquery in the FROM clause. This type of SQL statement is also known as an INLINE View. When this type of a SQL statement is used, the result set from this subquery becomes the data source for that particular SELECT statement. Figure 7.30 illustrates how this might work in looking for those employees that make less than the average salary. Notice how the subquery has a table alias name b.
Figure 7.30. FROM clause subquery example.
A related subquery is when a field in the inner query is referenced by the outer query. Figure 7.31 shows a related subquery being used to return the same result set as in Figure 7.30. Notice the table alias.
Figure 7.31. Related subquery example.
Top-N analysis is a convenient way to show the top five selling products, or in our case, the highest paid employees who are not sales people or the president. Top-N SQL queries rely on a subquery in the FROM clause and utilize the pseudo column ROWNUM. This ROWNUM is also a column that could be displayed as part of the SELECT clause, as illustrated in Figure 7.32.
Figure 7.32. Top-N analysis query example.
NOTE
A pseudo column is a table column that is not explicitly defined; that is, one comes with each table. ROWNUM is associated with the result set of queries.
Complex Views
Now that we understand subqueries, GROUP BY functions, and so on, the SELECT clause used to create the view can contain these features as well. Figure 7.33 illustrates creating and then selecting from a view that makes use of the GROUP BY functions.
Figure 7.33. Complex view example.
NOTE
This kind of view cannot accept DML SQL statements.
Views can also be created with the READ ONLY option (as illustrated in Figure 7.34) to ensure that there will be no DML SQL statements processed against them.
Figure 7.34. Read-only view example.
Read Consistency
The Oracle RDBMS has always supported read consistency. Read consistency ensures that the data will remain consistent for the duration of a SQL query from the start of the query. What this really means is if User A starts a query at 10:00 A.M. and User B makes an insert to data at 10:05A.M., when User A's process gets to the change of User B, the SQL statement will see the data as it existed prior to the change made by User B. Figure 7.35 illustrates read consistency as depicted in this example.
Figure 7.35. Read consistency example.
In any environment with more than one user trying to use resources, a mechanism for sharing must be established. Oracle9i uses various locking mechanisms to ensure that only one resource is updating a particular row at a time. Oracle supports two kinds of locking: exclusive and share. An exclusive lock would prevent any other user from making any changes to the object. A share lock allows multiple users to manipulate data in different parts of the object. Most locking is implicit, in that Oracle9I automatically locks rows, blocks, and tables for certain types of operations. Figure 7.36 shows an explicit share lock on the rows being affected by this parti-cular SQL statement. All locks are released with a COMMIT or ROLLBACK statement. Figure 7.37 shows the lock created by Figure 7.36 (user 11). You will learn more about locks and interpreting the information in Chapter 14, “Oracle9i Application SQL Tuning.”
Figure 7.36. Row-level locking example.
Figure 7.37. V$Lock example.
Oracle9i supports a new feature called flash back queries. This allows for a query user to invoke a read-consistent view of the database as of a certain date and time. This feature can be helpful to identify (or even build a recovery script!) accidentally deleted rows or to create a report before an update occurred. Listing 7.1 illustrates a query of the EMP table, an update, the same query showing the update and flash back enabled. Notice first the date and time. Then the query is first run, showing dept 10 having a combined salary of 8,750. The department 10 salaries are then doubled with the update SQL statement. The same query is run again showing the department salaries now of 17,500.
Figure 7.38 illustrates a SQL query before and after an update, showing the same results. Notice the execute dbms_flashback.enable_at_time syntax. The same query is again ran that shows the data as it appeared at 5:08.
Listing 7.1. Flash Back Query
SQL> select sysdate from dual;
SYSDATE
-- -- -- -- -
11-AUG-01
SQL> select to_char(sysdate,'hh:mm:ss') TIME from dual;
TIME
-- -- -- --
05:08:35
SQL> get test_flash
1 select dept.deptno, dname, sum(sal)
2 from emp, dept
3 where emp.deptno = dept.deptno
4* group by dept.deptno, dname
SQL> /
DEPTNO DNAME SUM(SAL)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
10 ACCOUNTING 8750
20 RESEARCH 10875
30 SALES 9400
SQL> update emp
2 set sal = sal * 2
3 where deptno = 10;
3 rows updated.
SQL> get test_flash
1 select dept.deptno, dname, sum(sal)
2 from emp, dept
3 where emp.deptno = dept.deptno
4* group by dept.deptno, dname
SQL> /
DEPTNO DNAME SUM(SAL)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
10 ACCOUNTING 17500
20 RESEARCH 10875
30 SALES 9400
SQL> execute dbms_flashback.enable_at_time ('11-AUG-01 05:08'),
PL/SQL procedure successfully completed.
SQL> get test_flash
1 select dept.deptno, dname, sum(sal)
2 from emp, dept
3 where emp.deptno = dept.deptno
4* group by dept.deptno, dname
SQL> /
DEPTNO DNAME SUM(SAL)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
10 ACCOUNTING 8750
20 RESEARCH 10875
30 SALES 9400
SQL> spool off
|