Scalar subqueries are a powerful enhancement to Oracle9i SQL. They allow for quick formulation of extremely complex SQL statements. Oracle’s introduction of scalar subquery support is another example of the company’s commitment to keeping pace with the evolution of the SQL language.
Oracle has long supported the notion of an “in-line view,” whereby a subquery can be placed in the FROM clause, just as if it were a table name. There’s an Oracle query displaying tablespace sizes in Listing A.
Listing B contains the output from this in-line view query against the data dictionary.
In the simple example in Listing A, the SQL subqueries are placed inside the FROM clause and assigned the aliases of df and fs. The df and fs subquery values are then referenced inside the SELECT clause. If you examine this query, you’ll see that it sums and compares two ranges of values from two tables, all in a single query. For some readers, seeing SQL inside the FROM clause is probably quite strange, and the scalar subquery is even stranger! The scalar subquery is a take-off of the in-line view whereby SQL subqueries can be placed inside the SELECT clause. Let’s take a look at a few examples.
Scalar subquery examples
Once you become acquainted with the syntax, you’ll find scalar subqueries to be very powerful. Scalar subqueries are especially useful for combining multiple queries into a single query. In Listing C, we use scalar subqueries to compute several different types of aggregations (max and avg) all in the same SQL statement. Note that this query uses both scalar subqueries and in-line views.
Scalar subqueries are also handy for inserting into tables, based on values from other tables. In Listing D, we use a scalar subquery to compute the maximum credit for BILL and insert this value into a max_credit table.
The scalar subquery in Listing D is quite useful for Oracle data warehouse applications. In an Oracle data warehouse, it’s common for the DBA to pre-aggregate values to speed up query execution, and scalar subqueries are a powerful helper in aggregation. In Listing E, we populate an emp_salary_summary table with many types of aggregate values from the base tables.
Restriction and usage
Scalar subqueries are restricted to returning a single value because they select a finite value. Scalar subqueries could be used in previous versions of Oracle in some parts of a SQL statement, but Oracle9i extends their use to almost any place where an expression can be used, including:
- · CASE expressions
- · SELECT statements
- · VALUES clauses of INSERT statements
- · WHERE clauses
- · ORDER BY clauses
- · Parameters of a function
There are also important restrictions on scalar subqueries. Scalar subqueries can’t be used for:
- · Default values for columns
- · RETURNING clauses
- · Hash expressions for clusters
- · Functional index expressions
- · CHECK constraints on columns
- · WHEN condition of triggers
- · GROUP BY and HAVING clauses
- · START WITH and CONNECT BY clauses
Receive weekly Oracle updates
Oracle, creator of the first Internet database platform, is a force to be reckoned with. Learn more about Oracle database administration and development in our e-newsletter, delivered each Wednesday. Sign up now!
Scalar subqueries simplify complex SQL queries
Scalar subqueries provide a powerful new tool within Oracle SQL. Their syntax is obtuse and sometimes hard to follow, but scalar subqueries can combine multiple queries into a single SQL unit, where they can be executed as a single unit. This greatly simplifies complex SQL computations. Scalar subqueries are especially useful for data warehouse applications and those types of databases requiring complex SQL queries.