Lesson 82 of 12120 min read

Subquery in SELECT Clause: Scalar Subqueries Explained

Learn how a scalar subquery in the SELECT clause computes a single extra value for every row of the outer query.

Author: CodersNexus

Subquery in SELECT Clause: Scalar Subqueries Explained

A subquery doesn't only filter data — it can also compute an additional column value directly within the SELECT list. This is called a scalar subquery, and it's a handy technique for adding a single computed value, like a company-wide average or a related count, right alongside each row's regular columns.

Key Definitions

  • Scalar subquery: A subquery placed in the SELECT clause that returns exactly one value, added as a computed column alongside the outer query's regular columns.

What You'll Learn

  • Define a scalar subquery and its guaranteed single-value result per row.
  • Add a computed column to a SELECT list using a scalar subquery.
  • Compare scalar subqueries against the alternative of using a JOIN with GROUP BY.
  • Recognize the performance consideration of scalar subqueries evaluated per row.

Detailed Explanation

Suppose you want to list every doctor alongside the company-wide average salary, for easy comparison. Writing `SELECT doctor_name, salary, (SELECT AVG(salary) FROM doctors) AS avg_salary FROM doctors` adds a third column, avg_salary, computed by the scalar subquery. Because the subquery here doesn't reference anything from the outer row, it only needs to be computed once and reused for every row.

A more advanced version references the outer row inside the subquery, becoming correlated (covered fully in Lesson 7.8) — for example, computing each doctor's total appointment count individually. `SELECT doctor_name, (SELECT COUNT(*) FROM appointments a WHERE a.doctor_id = d.doctor_id) AS appointment_count FROM doctors d` recalculates the count separately for every doctor, since a.doctor_id = d.doctor_id changes with each outer row.

Scalar subqueries in SELECT are a convenient alternative to a JOIN + GROUP BY for adding a single aggregated value, though for large tables, a correlated scalar subquery evaluated once per row can be less efficient than an equivalent JOIN-based aggregation — a tradeoff explored further in Lesson 7.13.

Visual Summary

A table with three columns: doctor_name, salary, and a highlighted fourth column avg_salary, with an arrow pointing from a small box labeled '(SELECT AVG(salary) FROM doctors) = 95250' into every row of the avg_salary column, showing the same computed value repeated for each row.

Quick Reference

Scalar Subquery TypeRe-evaluated Per Row?Example
Non-correlated scalar subqueryNo — computed once and reused(SELECT AVG(salary) FROM doctors)
Correlated scalar subqueryYes — recalculated for every outer row(SELECT COUNT(*) FROM appointments a WHERE a.doctor_id = d.doctor_id)

SQL Example


CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

-- Non-correlated scalar subquery: same average shown for every row
SELECT doctor_name, salary,
  (SELECT AVG(salary) FROM doctors) AS avg_salary
FROM doctors;

-- Correlated scalar subquery: per-doctor appointment count
SELECT
  d.doctor_name,
  (SELECT COUNT(*) FROM appointments a WHERE a.doctor_id = d.doctor_id) AS appointment_count
FROM doctors d;

The first query's avg_salary column shows the identical value, 95250, on every row, since the inner subquery has no dependency on the outer row and is computed just once. The second query's appointment_count column differs per doctor, since the inner subquery references d.doctor_id from the current outer row, forcing MySQL to re-evaluate it separately for each doctor.

Real-World Examples

  • E-commerce order listings use scalar subqueries to show each product alongside the category-wide average price for quick comparison.
  • HR dashboards use scalar subqueries to display each employee's salary next to the company-wide average in a single row.
  • Customer service platforms use correlated scalar subqueries to show each agent alongside their individual ticket count.

Common Mistakes to Avoid

  • Writing a SELECT subquery that could return multiple rows, causing a runtime error since only one value is allowed per row.
  • Not recognizing when a scalar subquery has silently become correlated, and being surprised its value differs across rows.
  • Using a correlated scalar subquery in SELECT for large tables where a JOIN + GROUP BY would perform significantly better.

Interview Questions

Q1. What is a scalar subquery?

A scalar subquery is a subquery placed in the SELECT clause that returns exactly one value per row, used to add a computed column alongside the outer query's regular columns.

Q2. What is the difference between a correlated and non-correlated scalar subquery in SELECT?

A non-correlated scalar subquery has no dependency on the outer row and is computed once, returning the same value for every row. A correlated scalar subquery references a column from the outer row, forcing it to be recalculated separately for each row.

Q3. What is a potential downside of using a correlated scalar subquery in SELECT for a large table?

Since it re-executes once per outer row, a correlated scalar subquery can become slow on large tables compared to an equivalent JOIN combined with GROUP BY, which typically computes the aggregation in a single pass.

Practice MCQs

1. Where is a scalar subquery placed?

  1. FROM clause
  2. SELECT clause
  3. GROUP BY clause
  4. ORDER BY clause only

Answer: B. SELECT clause

Explanation: A scalar subquery is placed directly in the SELECT list to compute a single additional value per output row.

2. What must be true about a scalar subquery's result?

  1. It must return multiple rows
  2. It must return exactly one value
  3. It must return a whole table
  4. It must always be correlated

Answer: B. It must return exactly one value

Explanation: A scalar subquery is required to return exactly one row and one column — a single value — to be validly placed in the SELECT list.

Quick Revision Points

  • A scalar subquery must return exactly one row and one column.
  • Non-correlated scalar subqueries in SELECT compute once; correlated ones recompute per outer row.
  • For large datasets, JOIN + GROUP BY is often more efficient than a correlated scalar subquery in SELECT.

Conclusion

  • A scalar subquery adds a single computed value as a column in the SELECT list.
  • Non-correlated scalar subqueries compute once; correlated ones recompute for every outer row.
  • Consider JOIN + GROUP BY as a potentially more efficient alternative for large tables.

A scalar subquery, placed directly within the SELECT clause, computes a single value that appears as an additional column alongside the outer query's regular data. When the subquery has no dependency on the outer row, it's computed once and reused for every output row; when it references an outer column, it becomes correlated and is recalculated separately for each row. While convenient for adding aggregated or related values without restructuring the whole query, correlated scalar subqueries can become a performance concern on large tables compared to an equivalent JOIN with GROUP BY.

Frequently Asked Questions

A scalar subquery is a subquery placed in the SELECT clause that returns exactly one value, used to add a computed column to each row of the outer query's result.

No, a scalar subquery must return exactly one row and one column. If it could return more, MySQL raises a runtime error.

A non-correlated scalar subquery doesn't reference the outer row and computes once, giving the same value for every row. A correlated scalar subquery references an outer column and recalculates separately for each row.

It can be, especially when correlated, since it re-executes once per outer row. For large tables, an equivalent JOIN combined with GROUP BY is often more efficient.

Scalar subqueries are convenient for adding a single computed value without restructuring the whole query, especially for smaller datasets or one-off reporting columns where readability matters more than raw performance.