A subquery that returns exactly one column value from one row is called a scalar subquery. A scalar subquery is a subquery that returns a single value. It is typically used in the SELECT, WHERE, or HAVING clause of a SELECT statement, or in the SET clause of an UPDATE statement.
For example, the following query uses a scalar subquery in the WHERE clause to find all rows where the value in the "Quantity" column is greater than the average quantity of all rows:
SELECT * FROM orders
WHERE Quantity > (SELECT AVG(Quantity) FROM orders);
In this example, the scalar subquery is (SELECT AVG(Quantity) FROM orders), which returns a single value representing the average quantity of all rows in the orders table. The outer query then uses this value to filter the rows in the orders table, returning only those rows where the value in the Quantity column is greater than the average quantity.