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.

Visualization of a subquery
Visualization of a subquery

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:

-- A subquery within the WHERE clause using an aggregate function
-- that returns a single aggregated value
SELECT Column1
FROM   Table1
WHERE  Column1 expr_operator (
       SELECT aggregate_function(Column2) 
       FROM   Table2);

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:

-- A subquery within the WHERE clause with a WHERE condition
-- that returns a single value
SELECT Column1
FROM   Table1
WHERE  Column1 expr_operator (
       SELECT Column2
       FROM   Table2
       WHERE  condition);

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.

Note: We can also use a subquery within a SELECT statement by adding it as a column instead of adding it to the WHERE clause. We can also add it to the FROM or any other clause as stated earlier.

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:

-- Create the Employees table
CREATE TABLE Employees (
    EmpID INT AUTO_INCREMENT PRIMARY KEY,
    EmpName VARCHAR(100),
    Salary DECIMAL(10, 2));
-- Insert sample data into Employees table
INSERT INTO Employees (EmpID, EmpName, Salary) VALUES
(1, 'Susan Lee', 50000),
(2, 'Alexa Smith', 60000),
(3, 'Sana Amberson', 45000),
(4, 'Sarah Ronald', 47000);

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:

SELECT e.EmpName, e.Salary
FROM   Employees e
WHERE  e.Salary > (
       SELECT AVG(e2.Salary)
       FROM Employees e2);

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.

Frequently asked questions

Haven’t found what you were looking for? Contact Us

What is the difference between a query and a subquery?

A query retrieves data directly from a database, while a subquery is a query embedded within another query, providing results for that parent query.

What is JOIN statement used for in MySQL?

The JOIN statement is used to combine row entries of two or more tables that are based on a matching column between them.

What is COALESCE() in SQL?

The COALESCE() in SQL is an in-built function that returns the first non-null value/expression in the given list of values/expressions.

Can we use join instead of subquery?

Yes, we can use a join instead of a subquery, depending on the specific requirements of the query. The choice between using a join or a subquery varies based on the context and the desired results.

Is subquery faster than join?

The performance is varies depending on the context. JOINs are generally faster, but subqueries can be quicker for specific use cases.

Free Resources

Copyright ©2026 Educative, Inc. All rights reserved