4. Understanding the Elements of SQL Statements : Subqueries : Scalar Subqueries
 
Share this page                  
Scalar Subqueries
A scalar subquery is a subquery that selects only one column or expression and returns one row. A scalar subquery can be used anywhere in an SQL query that a column or expression can be used.
A scalar subquery can be used in the following contexts:
The select list of a query (that is, the expressions between the SELECT and FROM keywords)
In a WHERE or ON clause of a containing query
The JOIN clause of a query
WHERE clause that contains CASE, IF, COALESCE, and NULLIF expressions
Source to an UPDATE statement when the subquery refers to more than the modified table
Qualifier to a DELETE statement where the subquery identifies the rows to delete
The VALUES clause of an INSERT statement
As an operand in any expression
Scalar subqueries can be used to compute several different types of aggregations (max and avg) all in the same SQL statement. The following query uses both scalar subqueries and in-line views:
SELECT
  (SELECT MAX(salary) FROM emp) AS highest_salary,
  emp_name AS employee_name,
  (SELECT AVG(bonus) FROM commission) AS avg_comission,
  dept_name
FROM emp, (SELECT dept_name FROM dept WHERE dept = ‘finance’) dept1;
Scalar subqueries can also be used for inserting into tables, based on values from other tables. The following examples uses scalar subquery to compute the maximum credit for Bill and insert this value into a max_credit table.
INSERT INTO max_credit (name,max_credit) VALUES (
   ‘Bill’,
   SELECT MAX(credit) FROM credit_table WHERE name = ‘Bill’
);
 
INSERT INTO emp_salary_summary
(sum_salaries, max_salary,min_salary, avg_salary)
VALUES (
   (SELECT SUM(salary) from emp),
   (SELECT MAX(salary) from emp),
   (SELECT MIN(salary) from emp),
   (SELECT AVG(salary) from emp));
A valid scalar subquery must produce at most a single value. That is, the result should consist of zero or one rows of one column.
If more than one row results, a cardinality error is raised. For more information, see Subquery Behavior Change due to Scalar Subquery Feature (see Subquery Behavior Change due to Scalar Subquery Feature).
Note:  A scalar subquery cannot be used as a parameter to a stored procedure call.