Author: Don Burleson
Scalar subqueries are a powerful enhancement to Oracle9i SQL. Oracle has long supported the notion of an in-line view, whereby a sub query SELECT can be placed in the FROM clause. Oracle9i has now extended this functionality to allow SQL subqueries inside the SELECT statement:
Embedded SELECT statements:
Below script shows embedded SELECT statements, inside outer SELECT and inside the FROM clause.
select
(select max(salary) from emp)
highest_salary, emp_name,
(select avg(bonus) from commission)
avg_comission, dept_name
from emp,
(select dept_name from dept where dept = ‘finance') ;
Oracle allows for single-row subqueries to be included in the SELECT clause of any SQL statement. While this ability may be of interest for obtuse queries, this feature is of little interest to Oracle9i developers.
Scalar subqueries inside DML:
Scalar subqueries can also be used by DML. Below script shows a subquery inside an insert statement
Insert into customer (name, max_credit) values ( ‘Sam', (select max(credit) from credit_table where name =SAM') );
Scalar subqueries can useful for streamlining INSERT and UPDATE statements that might otherwise be forced to use subqueries in their WHERE clause to get the required data.
In-line views:
Below Oracle query displays tablespace sizes. It uses SELECT statement in the FROM clause. This is called an in-line view.
select
df.tablespace_name "Tablespace",
block_size "Block Size",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace " Free MB ",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
dba_tablespaces ts,
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from dba_free_space
group by tablespace_name) fs
where
ts.tablespace_name = fs.tablespace_name
and
df.tablespace_name = fs.tablespace_name(+)
;
Tablespace Block Size Used MB Free MB Total MB Pct. Free
------------- ---------- ----------- ----------- ----------- ----------
CWMLITE 4,096 6 14 20 70
DRSYS 4,096 8 12 20 60
EXAMPLE 4,096 153 0 153 0
INDX 4,096 0 25 25 100
SYSTEM 4,096 241 84 325 26
TOOLS 4,096 7 3 10 30
TS_16K 16,384 3 7 10 70
UNDOT 4,096 1 199 200 100
USERS 4,096 1 24 25 96
Adding subqueries directly into the SELECT clause:
Oracle9i enables you to add subqueries directly into the SELECT clause. Below code selects the MAX and AVG values for a table, right along with the detail rows:
select
(select max(salary) from emp) highest_salary,
emp_name employee_name,
(select avg(bonus) from commission) avg_comission,
dept_name
from emp, (select dept_name from dept where dept = ‘finance')
;
Adding subqueries directly into the VALUES clause:
Subqueries can be applied directly into the VALUES clause of an insert statement.
insert into max_credit(name, max_credit)values
(‘Bill', select max(credit) from credit_table where name = ‘BILL' );
Benefits of Scalar subqueries:
- Scalar subqueries provide a powerful tool within Oracle SQL.
- Scalar subqueries are especially useful for combining multiple queries into a single query. They can be used to compute several different types of aggregations (max and avg) all in the same SQL statement.
- 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.
Limitations of Scalar subqueries:
Scalar subqueries have some limitations.
- Scalar subqueries cannot be used for default values for columns
- Scalar subqueries cannot be used for returning clauses
- Scalar subqueries cannot be used for hash expressions for clusters
- Scalar subqueries cannot be used for functional index expressions
- Scalar subqueries cannot be used for CHECK constraints on columns
- Scalar subqueries cannot be used for WHEN condition of triggers
- Scalar subqueries cannot be used for GROUP BY and HAVING clauses
- Scalar subqueries cannot be used for START WITH and CONNECT BY clauses
More Oracle Articles, Database Articles and DBA Tips
Database Security: Step by step guideline
A Guide for Individual Objects Tuning for Databases
Tips for Oracle 11g SQL Execution Plan Management!!
DBA Tips: Is your SYSMAN Account Locked??
Efficiently handle problematic Oracle Sequences!!
|
