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.

You might specify column aliases with the AS syntax. However, in a subquery, this is not always easy to read, especially when we are selecting many columns. Some database technologies allow to specify a correlation list after the subquery:

SELECT country, first_hire, last_hire
    FROM (
        SELECT country, MIN(hire_date), MAX(hire_date) FROM employee GROUP BY country
    ) AS v (country, first_hire, last_hire)
    WHERE country = 'FR'
;

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.

Temporay Tables

Temporary tables might be a bit out of the scope of this article, but it’s good to mention them. You can materialise a resultset into a query into a temporary table. Normally, temporary tables are only stored in-memory. This means that they’re fast, but it also means that we should not abuse this feature. Creating temporary tables for a one-off operation is normally fine, but creating big temporary tables often will damage the database performance.

To materialise a resultset using MariaDB syntax:

CREATE TEMPORARY TABLE highly_paid_employees 
    SELECT * FROM employee
    ORDER BY current_salary DESC
    LIMIT 10
;

PostgreSQL syntax is CREATE TEMPORARY TABLE AS, and it’s very similar.

After creating a temporary table you can add more rows with INSERT ... SELECT, or modify rows with ALTER TABLE, UPDATE, DELETE. It’s useful when, instead of just selecting columns or rows from a resultset as-is, you need to perform more complex data transformation.

The TABLE Command

As far as I know, this is command is only supported by MySQL. It’s a sort of syntax sugar that we can use when we want to select all the columns and rows from a table. For example:

SELECT AVG(current_salary) FROM (
    TABLE employee ORDER BY current_salary DESC LIMIT 10
);

Since this command doesn’t do anything that can’t be done in other ways, I recommend using the more standard SELECT.

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 perform more complex tranformations, we can use temporary tables.

To read my other 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