SQL Subquery

Introduction

In SQL, the subquery is a query within another query. Simply put, it is a query we can embed in the WHERE clause of another query. We can use a subquery in different statements.

Most importantly, we can place the subquery in several different clauses. Moreover, the outer query is called the main query and the inner query is known as the subquery. SQL subquery plays a vital part in SQL.

Generally, the subquery executes first when it has no relation with the main query. However, with the co-relation, the parser takes the fly decision, executing on precedence and using the output. Also, we need to enclose the subquery in parentheses.

Overview

Structured query language or SQL stores the data in relational databases. The relational databases store all the information in different columns and rows. It represents data attributes and the relation between them.

In addition, SQL statements can store, update, search, and retrieve information from the database. It is the best method to optimize database performance. Today, we will learn about SQL subqueries, different types of subqueries in SQL and how to use them.

What is a Subquery in SQL?

A subquery is nested inside a query. They are also called nested queries or inner queries. The SQL subquery retrieves the data, which we can use in the outer query.

In this case, we can execute the inner query first. The results can be used to evaluate the outer query. A developer should know that the order of inner and outer queries depends on the project or given scenario.

We can use the SQL subqueries in different statements such as INSERT, SELECT, UPDATE, etc. Also, we can adapt the queries in conjunction with several SQL operators. Here is a SQL subquery syntax example:

-- Selecting the list of users
-- with the longest nicknames
SELECT id, nickname
FROM users
WHERE LENGTH(nickname) > (
SELECT AVG(LENGTH(nickname))
FROM users
)

Here, the subquery is in the embedded form between the parentheses:

SELECT AVG(LENGTH(nickname))
FROM users

A MySQL subquery is a query nested within another query, typically used to retrieve or manipulate data based on the results of the outer query. The syntax for a MySQL subquery in select is as follows:

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

Why Should We Use the SQL Subqueries?

Subqueries are useful as they allow us to embed specific query logic into more general queries. Simply put, we can get the results of multiple queries by running a single query.

We can increase readability and performance by using different types of subqueries in SQL. Let's understand this with an SQL subquery example. To find users with above-average points, we can use this query:

SELECT id, nickname
FROM users
WHERE points > AVG(points)

However, we cannot use the aggregate operators inside the WHERE clause, and the above query can result in an error. Instead, we can find the average points and select all the users with greater points.

Launch this query,

-- getting the average number of points
SELECT AVG(points)
FROM users

Assume it to return as 420

Now, achieve the end goal with the below query

SELECT id, nickname
FROM users
WHERE points > 420

We can achieve similar results with a single query involving a subquery:

SELECT id, nickname
FROM users
WHERE points > (
SELECT AVG(points)
FROM users
)

Now, the DBMS engine will help to execute this subquery first,

SELECT AVG(points)
FROM users

It will replace the subquery with the result and execute the outer query accordingly. The logic can correspond to two queries, with a more readable one. Also, remember, correlated subquery in SQL can be handy in many situations.

Rules for the Subqueries:

It is essential to follow some rules to use SQL subqueries. Learn about some rules below:

• All the subqueries should be enclosed in parentheses
• We can nest subqueries within another subquery
• Subqueries must contain the SELECT and FROM clauses
• SQL subqueries can return a single row, column, or whole table
• It contains all the clauses that an ordinary SELECT clause contains, such as group by, where, having, top, limit, etc. However, we can use the ‘by clause’ to specify the top clause. Generally, it does not include the compute clause.
  

Subqueries With SELECT Statement

With the SQL subquery in select, we can use the basic form of the subquery:

(SELECT [DISTINCT] subquery_select_argument
FROM {table_name | view_name}
{table_name | view_name} ...
[WHERE search_conditions]
[GROUP BY aggregate_expression [, aggregate_expression] ...]
[HAVING search_conditions])

To demonstrate a subquery using the SELECT statement, let's consider the following example with a products_bkg table:

Let's say we want to find products that are cheaper than the average price of all products. We can use a subquery to calculate the average price first, then compare each product's price with this average.

The SQL query to achieve this would look like:

SELECT Product, Price
FROM products
WHERE Price < (SELECT AVG(Price) FROM products);

Subqueries With FROM Statement

We can use a FROM clause to specify a subquery statement in SQL. However, the result of which is stored in the temporary variable.

Let's consider a scenario where we want to fetch products from the products_bkp table where the quantity is greater than the average quantity in stock.

SELECT *
FROM products_bkp
WHERE quantity > (SELECT AVG(quantity) FROM products_bkp);

Output

Subqueries With INSERT Statement

Suppose we want to copy data from the products table into the products_bkp table using a subquery with the INSERT statement.

INSERT INTO products_bkp (ID, Product, Price, InStock)
SELECT ID, Product, Price, InStock
FROM products;

Subqueries With UPDATE Statement

In the update statement, we can set a new column value equal to the result returned by a single row.

Suppose we want to update the Price in the products table where the Price is more than the minimum Price. We'll set the Price to double the minimum Price.

UPDATE products
SET Price = (SELECT 2 * MIN(Price) FROM products)
WHERE Price > (SELECT MIN(Price) FROM products);

The command will produce this result,

Subqueries With DELETE statement

We can use a DELETE statement to delete different records. To delete data from the products_bkp table where the Price is less than the maximum Price, we can use the DELETE statement with a subquery:

DELETE FROM products_bkp
WHERE Price < (SELECT MAX(Price) FROM products_bkp);

How to Join Different Subqueries?

It is common for a developer to implement SQL subquery in join.

Here is an example to see how to join different subqueries.

SELECT *
FROM tutorial.sf_crime_incidents_2014_01 incidents
JOIN ( SELECT date
FROM tutorial.sf_crime_incidents_2014_01
ORDER BY date
LIMIT 5
) sub
ON incidents.date = sub.date

This can be useful when we combine it with aggregations. When we use the Join, the requirements for the subqueries are not as strict as the WHERE clause. For instance, an inner query can provide an output for multiple results. One of the SQL subquery examples below ranks all the results according to the incidents reported in one day. It aggregates the total incidents as an inner query, and then uses the values for the outer query:

SELECT incidents.*,
sub.incidents AS incidents_that_day
FROM tutorial.sf_crime_incidents_2014_01 incidents
JOIN ( SELECT date,
COUNT(incidnt_num) AS incidents
FROM tutorial.sf_crime_incidents_2014_01
GROUP BY 1
) sub
ON incidents.date = sub.date
ORDER BY sub.incidents DESC, time

Subquery Basics

With SQL subqueries, we can perform multiple steps as it acts as a performance tool. If we want to take the sum of serval columns and then the average of those columns, we need to follow aggregation in each step. Also, we can use subqueries in many places within a query. Ideally, we can use the FROM statement.

SELECT sub.*
FROM (
SELECT *
FROM tutorial.sf_crime_incidents_2014_01
WHERE day_of_week = 'Friday'
) sub
WHERE sub.resolution = 'NONE'

After breaking down the above query, the database runs in the inner query, which is a part between parentheses.

SELECT *
FROM tutorial.sf_crime_incidents_2014_01
WHERE day_of_week = 'Friday'

The inner query must run on its own as the database treats it as an independent query. Also, once the inner query runs, the outer query will run using the results from the inner query:

SELECT sub.*
FROM (
<<results from inner query go here>>
) sub
WHERE sub.resolution = 'NONE'

All the SQL subqueries need to have names, which we can add after parentheses.

Challenges When Using SQL Subquery

One of the main challenges of SQL subqueries is performance. It slows down the query execution time and consumes more resources. If we limit the subqueries and ensure they are related to the main query, we can avoid this.

Another point is readability. SQL subqueries make the code more intricate and tricky, especially if we nest them deeply or at multiple levels. We can improve the readability if we use comments and indentation.

Real-world Challenges and Solutions With Practical Examples Showcasing Complex SQL Subquery Scenarios

Now let’s look into some practical examples of complex SQL subquery scenarios.

1.Finding Employees With the Highest Salary in Each Department

To find the employee with the highest salary in each department, use a subquery to first find the maximum salary for each department, and then join this result back to the original table to get the corresponding employee details.

SELECT employee_id, employee_name, department_id, salary
FROM employees e
WHERE (department_id, salary) IN
(SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id);

This query retrieves the employee ID, name, department ID, and salary for each employee who has the highest salary in their respective department.

2. Calculating the Total Order Value for Customers with the Highest Order Amount

To find the total order value for customers who have placed the highest order amount, use a subquery to first find the maximum order amount, and then sum up the total order values for customers with that maximum amount.

SELECT customer_id, SUM(order_value) AS total_order_value
FROM orders
WHERE order_amount = (SELECT MAX(order_amount) FROM orders)
GROUP BY customer_id;

3. Identifying Products with Sales Higher Than the Average

To identify products with sales higher than the average sales across all products, use a subquery to calculate the average sales, and then filter out products with sales greater than that average.

SELECT product_id, product_name, sales
FROM products
WHERE sales > (SELECT AVG(sales) FROM products);

Hierarchical Data Retrieval Examples With SQL Subqueries

Here are some hierarchical data retrieval or data modification examples;

1. Hierarchical Data Retrieval: Retrieving Managers and Their Direct Reports

Suppose we have an employee table where each employee has a manager, and we want to retrieve the details of managers along with their direct reports. This involves using a subquery to match each manager's ID with their direct reporting manager ID. Here is how we can write it in SQL

SELECT
manager.employee_id AS manager_id,
manager.employee_name AS manager_name,
report.employee_id AS report_id,
report.employee_name AS report_name
FROM
employees AS manager
INNER JOIN
employees AS report ON manager.employee_id = report.manager_id;

2. Hierarchical Data Modification: Updating Parent Records Based on Child Records

Let's say we have a table representing a hierarchical structure of categories, where each category can have subcategories. We want to update the status of parent categories if any of their subcategories meet certain criteria. This involves using a subquery to identify subcategories that meet the criteria and then updating the parent categories accordingly.

UPDATE categories AS parent
SET parent.status = 'Updated'
WHERE EXISTS (
SELECT 1
FROM categories AS subcategory
WHERE subcategory.parent_id = parent.category_id
AND subcategory.criteria = 'met'
);

This query above updates the status of parent categories to 'Updated' if any of their subcategories meet the specified criteria.

3. Hierarchical Data Retrieval: Retrieving Comments and Their Replies

Consider a scenario where we have a table storing comments on a blog post, where each comment can have replies. We want to retrieve all comments along with their corresponding replies and this involves using a subquery to match each comment with its replies. Here is how we can write it in SQL:

SELECT
comment.comment_id,
comment.content AS comment_content,
reply.reply_id,
reply.content AS reply_content
FROM
comments AS comment
LEFT JOIN
comments AS reply ON comment.comment_id = reply.parent_comment_id;

Optimization Techniques for Subqueries

As mentioned above, SQL subqueries have challenges, but we can optimize them for good results.

• To make our subquery faster, we can use EXISTS instead of IN when we check for a value in the result.
• Use JOINS as an alternative to subqueries to combine data from multiple tables.
• If we need to use the same result set multiple times, we can use Common Table Expressions (CTEs).
• Reduce the Order and Size of the result set by using Order by and Limit clauses. Following this, we can speed up the query execution time.
• Apply index on necessary columns. With an index, we can create unique data columns without overlapping each other, improving the speed of data retrieval.

In SQL, optimization techniques such as Common Table Expressions (CTEs) or Window Functions can significantly enhance performance when working with subqueries.

1. Common Table Expressions (CTEs)

Common Table Expressions (CTEs) allow us to define temporary result sets that can be referenced within the main query. By breaking down complex queries into smaller, more manageable parts, CTEs can improve readability and maintainability. Moreover, the SQL engine can optimize the execution plan by evaluating the CTE only once, which can lead to better performance, especially in scenarios involving recursive or repetitive subqueries.

Suppose we have a table named orders containing order details, and we want to retrieve the total sales amount for each month. Using a CTE, we can first aggregate the sales data by month and then join it with the main query to retrieve additional details.

WITH MonthlySales AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(order_amount) AS total_sales
FROM
orders
GROUP BY
DATE_TRUNC('month', order_date)
)
SELECT
month,
total_sales,
-- Additional columns if needed
FROM
MonthlySales;

In this example, the CTE MonthlySales aggregates the total sales amount for each month. The main query then retrieves the month and total sales from the CTE. By using a CTE, we can avoid redundant calculations and improve query performance.

2. Window Functions

Window Functions enable us to perform calculations across a set of rows related to the current row, without the need for self-joins or subqueries. They can efficiently compute aggregated values, rankings, and cumulative sums, among other operations. By avoiding the need for multiple passes over the data, Window Functions contribute to better performance and scalability in SQL queries.

Consider a scenario where we have a table named employees containing employee details, and we want to rank employees based on their salaries within each department. We can utilize a window function to achieve this efficiently.

SELECT
employee_id,
employee_name,
department_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM
employees;

In this example, the RANK() window function assigns a rank to each employee within their respective department based on their salary. Using window functions eliminates the need for self-joins or subqueries, resulting in more concise and performant SQL code.

Best Practices to Use SQL Subquery

The best ways to efficiently use SQL Subquery are mentioned below.

• Avoid unnecessary subqueries, it's harder to debug, read, and optimize.
• Use appropriate data and design SQL subqueries efficiently.
• Split the queries into multiple queries to minimize the views in the Join.
  

Conclusion

We have provided a detailed explanation of SQL subqueries and how to use them in different SQL statements. A simple way to use the subquery is to execute it together in different forms. We can nest different types of subqueries in SQL to gain results.

FAQs:

1. What is a subquery in SQL?

A subquery is a query you can nest inside INSERT, SELECT, UPDATE, and DELETE statements.

2. How to join 3 subqueries in SQL?

To join three subqueries, you can use a nested SELECT statement, which will retrieve data from one table based on the values of the other table.

3. Why are subqueries used?

This is one of the important SQL subquery questions. Subqueries are used to add a new column to the main query result, to create a filter, or to create a consolidated source to select the data.

4. How many subqueries are there in SQL?

Primarily, there are five types of subqueries in SQL - single row, multiple two, correlated, nested, and multiple columns.

5. What is an example of a subquery?

You can use SQL subqueries with INSERT statements to insert selected rows.

INSERT INTO new_orders SELECT * FROM orders WHERE order_date >= '2023-01-01'; This query inserts rows from the "orders" table with an order date on or after January 1, 2023, into the "new_orders" table.

6. What is the difference between subquery and join?

Subqueries can filter rows and return values as a part of the outer query, and Join can help to combine rows from multiple tables based on columns.

7. Where can subqueries be used?

You can use subqueries to retrieve and compare data from many tables. Also, to filter and manipulate results.

8. Can you use 2 subqueries in a SQL query?

Yes, you can use more than two subqueries in SQL, including the outer query.

9. Which is faster join or subquery?

Join is faster than subquery as it can use optimization and indexes.

10. What is the limit of subquery?

The maximum number of subqueries on the side of the union is 50. However, you can nest up to 255 levels.