Scalar Subqueries
A scalar subquery (or scalar subselect) is a subquery that selects only one column or expression and returns one row.
Ingres has supported scalar subqueries in "expression comparison_op scalar subquery" syntax in a WHERE, ON, or HAVING clause. This enhancement provides full scalar subquery support. It allows scalar subqueries to be used anywhere in an SQL query that a column or expression can be used. For example, it can appear in the select-list, in a WHERE or ON clause of a containing query, or as an operand in any expression.
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)
• The JOIN clause of a query
• A WHERE clause that contains CASE, IF, COALESCE, and NULLIF expressions
• The source to an UPDATE statement when the subquery refers to more than the modified table
• A qualifier to a DELETE statement where the subquery identifies the rows to delete
• The VALUES clause of an INSERT statement
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’) v;
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));
To be a valid scalar subquery, the subquery must produce at most a single value. That is, the result should consist of zero or one row of one column. If more than one row results, a cardinality error occurs.
Note: For more information, see these sections in the SQL Reference Guide:
• Scalar Subqueries
• Subqueries in the Target List, SET, and VALUES Clauses
• Subquery Behavior Change due to Scalar Subquery Feature