Types of Subqueries in SQL

Last Updated: 3rd October, 2023

Subqueries, also known as nested queries, are a powerful feature in SQL that allows you to perform complex operations by nesting one query within another. They enable you to target specific rows and perform various operations in SQL, such as SELECT, UPDATE, INSERT, and DELETE. Subqueries come in various types, each serving a unique purpose in data manipulation. In this article, we will explore the different types of subqueries in SQL using practical subquery in SQL examples and provide insights into their applications.

Introduction

Structured Query Language (SQL) is the foundation of managing and manipulating relational databases, allowing us to extract meaningful insights and valuable information from vast datasets. Within the realm of SQL, subqueries stand as a versatile and indispensable tool for unleashing the full potential of database systems.

Subqueries in SQL, often referred to as nested queries, represent a dynamic and sophisticated facet of SQL. They empower database professionals and developers to explore the intricate relationships within data, facilitating intricate operations and enabling precise data extraction. Subqueries are not merely a feature of SQL; they are the embodiment of analytical thinking and data-driven decision-making.

These nested queries bring forth a realm of possibilities, offering a means to scrutinise and traverse data hierarchies, refine results with precision, and pave the way for efficient data modification. Whether you seek to perform complex calculations, correlate datasets, or filter records based on intricate criteria, subqueries serve as your trusted companions in the journey through the database landscape.

In this comprehensive exploration, we delve into the depths of SQL subqueries, dissecting their various forms and demonstrating their real-world applications. As we navigate through the intricacies of single-row and multiple-row subqueries, delve into the nuances of correlated subqueries, and unlock the power of nested subqueries, you'll embark on a journey that enhances your ability to harness the full potential of SQL.

Through practical subquery example in sql and structured insights, this article illuminates the path to mastering SQL subqueries. Join us in unravelling the complexity of nested queries, and empower yourself to transform raw data into actionable intelligence.

Types of Subqueries in SQL with Examples

Scenario: E-Commerce Store and Employee Management

In the bustling world of modern e-commerce, there exists an innovative and dynamic online store that caters to the diverse needs and desires of its customers. This online marketplace offers a wide range of electronic products, from cutting-edge laptops to sleek smartphones and stylish tablets. Let's dive into the narrative of this thriving e-commerce store and its dedicated team of employees.

Products Table:

The "Products" table represents the heart and soul of the online store's offerings. Each product is carefully curated and priced to meet the demands of tech-savvy shoppers. The "product_id" uniquely identifies every item, while the "product_name" provides a glimpse into its specifications and features. The "price" column reveals the cost at which these technological marvels are made available to customers.

Employees Table:

Behind the scenes, a dedicated workforce drives the operations of the online store. The "Employees" table introduces us to the talented individuals who manage various aspects of the business. Employee details include their "employee_id," "last_name," "job_id" (highlighting their roles within the organization), "salary" (reflecting their compensation), and "department_id" (indicating the department to which they belong).

Departments Table:

Within the e-commerce company, different departments collaborate to ensure smooth functioning. The "Departments" table sheds light on these organizational units. Each department is uniquely identified by its "department_id," and the "department_name" provides insight into its purpose. The "location_id" indicates the geographical location or region in which the department operates, whether in the United States (US) or the United Kingdom (UK).

Job History Table:

As employees progress in their careers, their roles may evolve. The "Job History" table maintains a historical record of these transitions. It records "employee_id," "job_id" (indicating their past or current roles), and "department_id" (revealing the department associated with each role change).

Orders Table and Order Items Table:

In the e-commerce realm, customer orders are the lifeblood of the business. The "Orders" table stores essential order information, including the "order_id" (a unique identifier for each order), "customer_id" (identifying the customer making the purchase), and "order_total" (the total amount of the order).

The "Order Items" table provides a granular view of each order, detailing the specific products and quantities included in each purchase. It references "order_id" to associate each item with its respective order and lists "product_id" and "quantity" to reveal the products selected by customers and the quantities ordered.

Products Table

Employees Table

Departments Table

Job History Table

Orders Table

Order Items Table

Now let us explore the various types of subquery in SQL using these example tables.

Single-Row Subquery

Single-row subqueries return a single row of data as output to the parent query. They are typically used with SQL SELECT statements and can appear in clauses like WHERE, HAVING, or FROM.

Example:

SELECT product_name,
       price,
       (SELECT AVG(price)
        FROM products) AS avg_price
FROM products;

Output:

In this example, the single-row subquery calculates the average price of all products (700.00) and displays it alongside each product's price.

Multiple-Row Subquery

Multiple-row subqueries return multiple rows of data as output to the parent query. They can be used with SQL SELECT statements and clauses like WHERE, HAVING, FROM, and logical operators.

Example:

SELECT AVG(salary)
FROM employees
WHERE department_id IN (SELECT department_id
                        FROM departments
                        WHERE location_id = 'US');

Output:

This query uses a multiple-row subquery to calculate the average salary of employees in departments located in the 'US' (departments with location_id = 'US'). The result is an average salary of approximately 6166.67.

Multiple-Column Subquery

Multiple-column subqueries return multiple columns as output to the parent query. They are used to retrieve sets of data that include multiple columns.

Example:

SELECT employee_id, last_name, job_id
FROM employees
WHERE (job_id, department_id) IN
      (SELECT job_id, department_id
       FROM job_history);

Output:

This query employs a multiple-column subquery to retrieve employee information based on their job history. It selects employees whose (job_id, department_id) matches records in the job_history table.

Correlated Subquery

Correlated subqueries return multiple columns as output and depend on information from the parent query. They can be used in SELECT statements using WHERE and FROM clauses.

Example:

SELECT department_name,
       (SELECT AVG(salary)
        FROM employees e
        WHERE d.department_id = e.department_id) AS avg_salary
FROM departments d;

Output:

In this query, the correlated subquery calculates the average salary for each department based on the department ID in the parent query. However, it returns (NULL) for departments with no employees in the sample data.

Nested Subquery

Nested subqueries are subqueries within other subqueries in SQL. They are executed in a nested manner, with the innermost subquery executed first, followed by the outer ones.

Example:

SELECT order_id,
       customer_id,
       (SELECT SUM(order_total)
        FROM order_items
        WHERE order_id = orders.order_id) AS total_order_amount
FROM orders;

Output:

This query demonstrates a nested subquery that calculates the total order amount for each order based on data from the "order_items" table. The output includes the order ID, customer ID, and the total order amount for each order.

Additional Examples

Let's explore more SQL subquery examples using different tables:

Products Table

Employees Table

Departments Table

Job History Table

Orders Table

Order Items Table

Subqueries with INSERT Statement

Subqueries can be used with the INSERT statement to insert selected rows from one table into another.

Example:

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.

Subqueries with UPDATE Statement

Subqueries can be used with the UPDATE statement to set new values in a column based on the output of the subquery.

Example:

UPDATE employees
SET salary = salary * 1.1
WHERE department_id IN (SELECT department_id
                        FROM departments
                        WHERE location_id = 'US');

This query updates the salaries of employees in departments located in the 'US' by increasing them by 10%.

Subqueries with DELETE Statement

Subqueries can also be used with the DELETE statement to delete specific rows based on the subquery's output.

Example:

DELETE FROM employees
WHERE employee_id IN (SELECT employee_id
                     FROM job_history
                     WHERE job_id = 'Manager');

This query deletes employees whose job history includes a 'Manager' role.

Conclusion

In this comprehensive exploration of SQL subqueries, we've covered various subquery types, their practical applications, and provided illustrative examples. Subqueries are a versatile tool in SQL, enabling complex data retrieval and manipulation tasks. Understanding these different subquery types and how to use them effectively empowers you to perform advanced operations on your data efficiently. As you gain more experience and practice, you'll become a proficient SQL learner.

Key Takeaways

• Subqueries, also known as nested queries, are a powerful feature in SQL that enable complex data manipulation by nesting one query within another.
• There are several types of subqueries in SQL, each serving a specific purpose:
  • Single-Row Subquery: Returns a single row of data as output to the parent query. Typically used with SQL SELECT statements.
  • Multiple-Row Subquery: Returns multiple rows of data as output to the parent query and can be used with various SQL clauses and logical operators.
  • Multiple-Column Subquery: Returns multiple columns of data and is used to retrieve sets of data that include multiple columns.
  • Correlated Subquery: Returns multiple columns and depends on information from the parent query, often used in SELECT statements with WHERE and FROM clauses.
  • Nested Subquery: Consists of subqueries within other subqueries, executed in a nested manner.
• Subqueries can be used for data retrieval as well as for data modification with INSERT, UPDATE, and DELETE statements.
• Understanding the various types of subqueries and how to use them effectively empowers SQL users to perform advanced data operations efficiently.

Quiz

1. What is the primary purpose of a correlated subquery in SQL?

A) To return a single-row result

B) To return multiple rows of data

C) To calculate an average value

D) To depend on information from the parent query

Answer: D) To depend on information from the parent query

2. Which type of subquery returns a single row of data as output to the parent query?

A) Multiple-Row Subquery

B) Multiple-Column Subquery

C) Correlated Subquery

D) Single-Row Subquery

Answer: D) Single-Row Subquery

3. In SQL, what does a multiple-column subquery return as output to the parent query?

A) A single value

B) Multiple rows of data

C) Multiple columns of data

D) Correlated data

Answer: C) Multiple columns of data

4. What does a nested subquery in SQL involve?

A) Subqueries that cannot be executed

B) Subqueries that are unrelated to each other

C) Subqueries executed in a nested manner

D) Subqueries with no parent query

Answer: C) Subqueries executed in a nested manner

5. Which SQL statement can be used with subqueries to insert selected rows from one table into another?

A) SELECT

B) UPDATE

C) INSERT

D) DELETE

Answer: C) INSERT