A subquery is a query that appears inside another query.

WITH clause

-- Defining a subquery that can be reused many times within the main query
WITH temp AS (
    SELECT *
    FROM table1_name
)
SELECT *
FROM temp
WHERE column_name BETWEEN 2 AND 5; 
                                    

The WITH clause creates a temporary result set that can be referenced multiple times within a single query, while normal subqueries can be more straightforward for simpler tasks.

Subqueries in SELECT

Subqueries in the SELECT clause retrieve data from another table and display it as a calculated column in the outer query.

SELECT id, column1_name, 
(SELECT AVG(column2_name) 
    FROM table2_name
    WHERE table_name.id = table2_name.id) AS avg
FROM table_name;
                                    

Subqueries in FROM

Subqueries in the FROM clause specify the data source for the outer query. The subquery acts as a temporary table.

SELECT id, AVG(avg)
FROM (
    SELECT id, SUM(amount) AS avg
    FROM table_name
    GROUP BY id
) AS subquery
GROUP BY id;
                                    

Subqueries in WHERE

Subqueries in the WHERE clause filter results based on the output of another query.

SELECT column1_name, column2_name
FROM table_name
WHERE column2_name > (
    SELECT AVG(column2_name)
    FROM table_name
);
                                    

When building subqueries, we can use the IN and NOT IN keywords to check whether a certain value figures in the result of another query: SELECT * FROM table1_name WHERE column_name NOT IN (SELECT * FROM table2_name);.

Subqueries in HAVING

Subqueries in the HAVING clause are used with grouped results (GROUP BY) to filter groups based on the output of another query.

SELECT column1_name, AVG(column2_name) 
FROM table_name
GROUP BY column1_name
HAVING AVG(column2_name) > (
    SELECT AVG(column2_name)
    FROM table2_name
);