What is a scalar subquery in MySQL?

Key takeaways:

• A subquery is a query nested within another query.

• Scalar subquery is a specific type of subquery that returns a single value—one row and one column.

• The syntax for a scalar subquery often includes an aggregate function for comparison, and it can also be structured within the WHERE clause to return specific aggregated value.

• Scalar subqueries simplify complex calculations and comparisons by allowing single-value retrieval without needing complex joins.

A subquery is a feature in an SQL database that allows writing a query within a query. In a subquery, the outer query, also called the parent query, filters the data based on results from another query. A subquery is also known as a nested query or inner query. It is useful when performing operations based on related data between one or more tables.

Scalar subquery

Subqueries can be categorized into several types, each serving different purposes. One important type is the scalar subquery, which is a specific type of subquery that returns a single value—one row and one column.

Scalar subqueries are often used in the SELECT, INSERT, UPDATE, and DELETE statements. It can also be used with FROM, WHERE, and HAVING clauses. It can also be part of an expression or a component of a query, such as in calculations or comparisons. Scalar subqueries are particularly useful when we need to use calculated values in our queries without requiring complex joins or multiple queries.

How to use scalar subquery

The syntax of the scalar subquery is as follows. To utilize a subquery, we can arrange the queries in multiple ways. The first syntax involves a subquery within the WHERE clause using an aggregate function that returns a single aggregated value for comparison. Let’s look at the syntax for writing a subquery below:

Code explanation

The explanation of the syntax is given below:

• Lines 3–5: We retrieve Column1 from Table1 based on the value returned by the subquery.

• Lines 6–7: We compute the aggregate value of Column2 from Table2, which returns only one row and one column.

The second syntax involves a subquery within the WHERE clause with a WHERE condition that returns a specific single value for comparison. Let’s look at another syntax for writing a subquery below:

Code explanation

The explanation of the syntax is given below:

• Lines 3–5: We retrieve Column1 from Table1 based on the value returned by the subquery.

• Lines 6–8: We use a WHERE clause that retrieves a single value of Column2 from Table2 based on the condition, which returns only one row and one column.

Coding example: Scalar subquery

Let’s set up the database before having a look at the query. Let’s create a table to demonstrate the concept properly:

Explanation:

The explanation of the above code is given below:

• Lines 1–5: We have a table, Employees. The Employees table has columns EmpID, EmpName, and Salary.

• Lines 8–12: We insert sample data in the Employees table.

We’ll write a subquery to find employees earning more than the average salary. We used only one table because we can connect a table to itself using a subquery. Let’s look at the following query:

Code explanation

The explanation of the query is given below:

• Lines 1–3: We retrieve EmpName and Salary from the Employees table, aliased as e, and filter the results to include only employees whose salaries are greater than the average salary calculated in the subquery.

• Lines 4–5: The subquery computes the average salary from the Employees table, aliased as e2, which is then used as a threshold for comparison in the WHERE clause of the main query.

Conclusion

Scalar subqueries let us perform complex calculations and get single values for comparisons without needing joins or multiple queries. They are particularly useful for scenarios where we want to compare individual values against computed metrics from our data.