Understanding the SQL “SELECT FROM a SELECT” Query

Understanding the SQL “SELECT FROM a SELECT” Query


A common and useful feature in SQL is the ability to use a “SELECT FROM a SELECT” query, often referred to as a subquery or inner query.

This post aims to explain this concept, showing how it can be both powerful and practical for complex data retrieval tasks.

What is a “SELECT FROM a SELECT” Query?

A “SELECT FROM a SELECT” query, or subquery, is a SQL technique where an inner SELECT statement is nested within an outer SELECT statement.
This allows for more complex queries than can be achieved with a single SELECT statement.

Why Use Subqueries?

  1. Complex Filtering: Subqueries can filter data based on complex conditions that aren’t possible with a single SELECT.
  2. Data Organisation: They can be used to organise data in a way that’s easier to understand and manipulate.
  3. Performance Optimisation: In some cases, subqueries can be used to optimise the performance of a SQL query.

Basic Structure

The basic structure of a “SELECT FROM a SELECT” looks like this:

SELECT column_names
    SELECT column_names
    FROM table_name
    WHERE conditions
) AS subquery_name
WHERE outer_conditions;

Examples and Use Cases

Filtering on Aggregate Functions: Let’s say you want to find all departments where the average salary is above a certain threshold:

SELECT department
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_salaries
WHERE avg_salary > 50000;

Temporary Tables: Sometimes, you need to create a temporary table for further analysis:

    SELECT employee_id, COUNT(order_id) AS total_orders
    FROM orders
    GROUP BY employee_id
) AS employee_orders
WHERE total_orders > 10;

Layered Data Filtering: In more complex databases, you might need to filter data through multiple layers:

SELECT name, age
    SELECT name, age, department
    FROM employees
    WHERE age > 30
) AS older_employees
WHERE department = 'HR';

Best Practices

  1. Readability: Always aim for clarity. Subqueries can make SQL statements complex, so proper formatting and commenting are key.
  2. Performance: Keep an eye on performance. Subqueries can sometimes slow down a query, so it’s essential to test and optimise.
  3. Testing: Test subqueries independently to ensure they return the expected results.


The “SELECT FROM a SELECT” query is a powerful aspect of SQL that allows for advanced data manipulation and retrieval.

By understanding how to effectively use this technique, you can handle complex data scenarios with greater ease and efficiency.


Hi, my name is Stephen Finchett. I have been a software engineer for over 30 years and worked on complex, business critical, multi-user systems for all of my career. For the last 15 years, I have been concentrating on web based solutions using the Microsoft Stack including ASP.Net, C#, TypeScript, SQL Server and running everything at scale within Kubernetes.