tags: #sql/complex_queries

What are nested queries?

Nested queries, also known as subqueries, are queries that are embedded within another query. Subqueries can be used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved.

How can we build nested queries?

1. Using the IN Comparison Operator

We can build nested queries using the SELECT-FROM-WHERE blocks within the WHERE Clause of the main query and restrict the retrieved data using the IN comparison operator.

This compares the value, $v$, to the set of values, $V$, retrieved in the subquery, and evaluates to True if the value is an element in $V$.

⚠ Switch to EXCALIDRAW VIEW in the MORE OPTIONS menu of this document. ⚠

Text Elements

SELECT column1, column2, ...
FROM table1
WHERE column1 IN (SELECT column1
                  FROM table2
                  WHERE condition);

// Example
SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE (Pno, Hours) IN ( // subquery
	SELECT Pno, Hours
	FROM WORKS_ON
	WHERE ESSN = "123456789"
	)

What are we doing in the example?

• We want to retrieve all distinct employee (identified by their ESSN) where their project number and hours worked on, corresponds to the same project and hours worked on as the employee with the essn = 123456789.
• i.e., only ESSN values that have a corresponding Pno and Hours value in the subquery will be selected in the outer query.

Comparing multiple values

We can use a tuple of values in comparisons by placing them in parenthesis:

SELECT column1, column2, ...
FROM table1
WHERE (column1, column2) IN (SELECT column1
                  FROM table2
                  WHERE condition);

2. Using ANY (SOME)

We can use ANY (SOME) to compare $v$ to the $V$ (value set retrieved from the subquery) and evaluates to TRUE if $v$ is equal to any of the returned values in the value set by the subquery.

• This is equivalent to using the IN operator above.

SELECT column_name(s)
FROM table_name
WHERE condition operator <ANY/SOME> (
	SELECT column_name(s)
	FROM table_name
	WHERE condition
);

3. Using ALL

When using the ALL keyword in a nested query, the condition in the outer query is only satisfied if the comparison with the result set of the subquery evaluates to true for all rows in the result set.

SELECT column1, column2, ...
FROM table1
WHERE condition operator ALL (SELECT column1, column2, ...
                              FROM table2
                              WHERE condition);

4. Subqueries after FROM

You can write subqueries after the FROM clause to specify the data which you want to perform your query on. This retrieves the data form the specified data in the result set, wherein the outer query will retireve data from.

SELECT column1, column2, ...
FROM (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
) AS subquery_alias -- to refer to the result set in the outer query;

Collapsing Nested Queries

Queries that are nested using the IN comparison operator can be collapsed into one single block by using the JOIN operator to combine the results of the two queries.

// nested
SELECT *
FROM table1
WHERE column1 IN (
    SELECT column2
    FROM table2
)

// join
SELECT *
FROM table1
JOIN table2
ON table1.column1 = table2.column2

The JOIN operator combines the two tables based on the condition specified in the ON clause. In this case, we are joining table1 and table2 on the condition table1.column1 = table2.column2. This will return all rows from table1 where column1 matches a value in column2 of table2.