In SQL, everything is a table. Even the results of a query are a table. This means that you can select rows and columns from the results of another query. Sometimes this allows us to do things that would otherwise be impossible. For example, joining the results of an aggregating query (SELECT ... GROUP BY) with a table.

This article will show you all the ways to do this in SQL. We’ll use the MariaDB syntax, but it should work with any database with little or no changes.

Nested queries

The first method is trivial. When a query reads data from a table, the table name is indicated in the FROM clause. Instead of the table name we can specify a nested query, between parenthesis:

SELECT id, name, surname
    FROM (
        SELECT * FROM employee
        ORDER BY current_salary DESC
        LIMIT 10
    ) AS v
    WHERE country = 'FR'
;

We can use the same syntax with joins:

SELECT
    v1.id, v1.name, v1.surname,
    ec.contact_type, ec.contact
    FROM (
        SELECT * FROM employee
        ORDER BY current_salary DESC
        LIMIT 10
    ) AS e
    LEFT JOIN employee_contact ec
        ON e.id = ec.employee_id
    WHERE e.country = 'FR'
;

In the above example, we joined a query results to a regular table. But nothing prevents us from joining two queries.

Nested queries, or subqueries, can be placed in other parts of a query and be used for different goals. But this is out of the scope of this article.

The WITH syntax

Writing a query between parenthesis in place of a table name is intuitive, but the resulting query can be hard to read. A better way to do this is to set a name for each inner query we’re going to use, and then use query names as if they were table names.

WITH highly_paid_employees AS (
    SELECT * FROM employee
    ORDER BY current_salary DESC
    LIMIT 10
)
SELECT *
    FROM highly_paid_employees
    WHERE country = 'FR'
;

This syntax is also useful to run recursive queries, but this is beyond the purpose of this article.

Views

WITH allows us to give a query a name and mention it as if it was a table. But obviously, this name is only valid for the current query. If we know that we’ll want to select from a query result again in the future, we can create a view instead. A view is a permanent named query.

CREATE VIEW highly_paid_employees AS (
    SELECT * FROM employee
    ORDER BY current_salary DESC
    LIMIT 10
);

In the future, when you need to query this view, you can mention it as you would do with a table:

SELECT *
    FROM highly_paid_employees
    WHERE country = 'FR'
;

It’s important to know that, in some cases, databases run queries that mention views in non-optimal ways, not using indexes that could otherwise be used. This can result in slow queries.

Conclusions

Sometimes, instead of selecting data from a table, we want to run a SELECT on the results of another SELECT. It could be the only way to achieve what we want, it could be faster, or it could be just easier.

SQL provides the following ways to query the results of a query:

• Instead of a table name, write a query between parenthesis. This is the most intuitive way.
• Define a query with a name using the WITH clause. The result is the same, but this method makes the query easier to read and maintain.
• Create a view to define a persistent name for a query, and be able to use it in the future without specifying the query itself.

To read other my posts on the SQL language, use the corresponding website category or click this link. Consider our services if you seek help with SQL.

Federico Razzoli