Scalar subquery expressions

A scalar subquery expression is a subquery that returns a single row and a single column.

The syntax for a scalar subquery expression is as follows:

( subquery )

If a subquery returns 0 rows, the value of the scalar subquery expression is NULL. If a subquery returns multiple rows, an error is returned by the database. Scalar subquery expressions can be used as parameters in most expressions.

A scalar subquery is an invalid expression in the following situations:

• As the default value for a column.
• In the GROUP BY clause.
• In the ORDER BY clause (in specific scenarios).
• In a CHECK constraint.

Examples

obclient> CREATE TABLE employees (name VARCHAR(20), salary DECIMAL(10, 2));
obclient> INSERT INTO employees VALUES ('Alice', 8000), ('Bob', 5000), ('Carol', 8000);

obclient> SELECT name, (SELECT MAX(salary) FROM employees) AS max_salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);