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.