Subqueries

a query within a query.

When do you need to use a subquery?

You need to use a subquery when you have the need to manipulate an existing table to "pseudo-create" a table that is then used as a part of a larger query.

Components	Subqueries	Joins
Combine data from multiple tables into a single result
Create a flexible view of tables stitched together using a “key”
Build an output to use in a later part of the query
Subquery Plan: What happens under the hood

Subquery Strategy

Subquery Basics

Subquery Basics

When a subquery, typically in the form of a nested or inline subquery, is correlated to its outer query, it cannot run independently. This is most certainly an edge case since correlated subqueries are rarely implemented compared to standalone, simple subqueries.

Subquery: Placement

• WITH: This subquery is used when you’d like to “pseudo-create” a table from an existing table and visually scope the temporary table at the top of the larger query.

A Common Table Expression (CTE) in SQL allows you to define a temporary result, such as a table, to then be referenced in a later part of the query.

• Nested: This subquery is used when you’d like the temporary table to act as a filter within the larger query, which implies that it often sits within the WHERE clause.

• Inline: This subquery is used in the same fashion as the WITH use case above. However, instead of the temporary table sitting on top of the larger query, it’s embedded within the FROM clause.

• Scalar: This subquery is used when you’d like to generate a scalar value to be used as a benchmark of some sort.

Nested & Scalar subqueries often do not require aliases the way WITH & Inline subqueries do.

WITH

• When a user wants to create a version of an existing table to be used in a larger query

WITH average_price AS
( SELECT brand_id, AVG(product_price) AS brand_avg_price
  FROM product_records
),
SELECT a.brand_id, a.total_brand_sales, b.brand_avg_price
FROM brand_table a
JOIN average_price b
ON b.brand_id = a.brand_id
ORDER BY a.total_brand_sales DESC;

Nested

• When a user wants to filter an output using a condition met from another table.

SELECT *
FROM students
WHERE student_id
IN (SELECT DISTINCT student_id
    FROM gpa_table
    WHERE gpa > 3.5);

Inline

• Inline subqueries create a “pseudo table” that aggregates or manipulates an existing table to be used in a larger query.

SELECT dept_name, max_gpa
FROM department_db x
     (SELECT dept_id, MAX(gpa) AS max_gpa
      FROM students
      GROUP BY dept_id
      ) y
WHERE x.dept_id = y.dept_id
ORDER BY dept_name;

Scalar

• It selects only one column or expression and returns one row, used in the select clause of the main query

SELECT 
   (SELECT MAX(salary) 
    FROM employees_db) AS top_salary,
   employee_name
FROM employees_db;

• If a scalar subquery does not find a match, it returns a NULL.

• If a scalar subquery finds multiple matches, it returns an ERROR.

For example, when you’d like to calculate the average salary across an entire organization to compare to individual employee salaries. Because it’s often a single value that is generated and used as a benchmark, the scalar subquery often sits within the SELECT clause.

Advantages:

• Readability: WITH and Nested subqueries are most advantageous for readability.

• Performance: Scalar subqueries are advantageous for performance and are often used on smaller datasets.

Subquery: Dependencies

• Simple Subquery: The inner subquery is completely independent of the larger query.

• Correlated Subquery: The inner subquery is dependent on the larger query.

When to use Correlated Query ?

When the value of the inner query is dependent on a value outputted from the main query (e.g., the filter statement constantly changes).

SELECT first_name, last_name, GPA, university
FROM student_db outer_db
WHERE GPA >
    (SELECT AVG(GPA) 
    FROM student_db 
    WHERE university = outer_db.university);

In the example above, you’ll notice that the value of the inner query -- average GPA -- keeps adjusting depending on the university the student goes to. THAT is a great use case for the correlated subquery.

CoRelated Subquery	Non-CoRelated Subquery
The inner query or subquery references the outer query.	The inner query doesn't depend on the outer query and can run as a stand-alone query.
The outer query executes before the inner query.	The inner query executes before the outer query.

Correlated subqueries are slower than Non-Correlated subqueries and should be avoided as much as possible.