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
emp.salary,
(SELECT MAX(salary) FROM emp mx WHERE emp.dept = mx.dept) AS highest_salary,
MAX(nondept.salary) max_nondept_salary,
empname AS employee_name,
(SELECT bonus FROM commission WHERE commission.dept = emp.dept) AS bonus,
DECIMAL((SELECT AVG(bonus) FROM commission WHERE bonus != 0),8,3) AS avg_commission,
dept_name
FROM emp, (SELECT dept_name FROM dept WHERE dept = emp.dept) dept1,
(SELECT MAX(salary) FROM emp mx where emp.dept != mx.dept) nondept(salary)
GROUP BY 1,2,4,5,6,7;
Scalar subqueries can also be used for inserting into tables, based on values from other tables. The following example 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.
Note: Vector does not perform a cardinality check on scalar subqueries. To avoid potentially wrong results, make sure the scalar subquery returns no more than one row.