SQL - Sub Queries

A subquery is a query that is embedded within another query. It is a powerful tool for retrieving data and can be used in a variety of ways, such as to filter data, calculate values, or join tables. Here are some common types of subqueries and examples of how they are used:

Scalar Subqueries: A scalar subquery is a subquery that returns a single value. It can be used in a variety of ways, such as to filter data or calculate a value. Here's an example:

In this example, the subquery calculates the total amount spent by each customer, and the outer query returns the customer name and the total amount spent.

Subqueries in the WHERE Clause: A subquery can be used in the WHERE clause to filter data based on a condition. Here's an example:

In this example, the subquery returns a list of customer IDs for orders placed after January 1, 2022. The outer query then returns the names of the customers who placed those orders.

Correlated Subqueries: A correlated subquery is a subquery that refers to a column in the outer query. It can be used to filter data or join tables. Here's an example:

In this example, the subquery returns the number of orders placed by each employee. The outer query then returns the employee name and the number of orders.

Subqueries in the FROM Clause: A subquery can be used in the FROM clause to create a temporary table that can be joined with other tables. Here's an example:

In this example, the subquery creates a temporary table that summarizes the average order quantity for each product. The outer query then joins this temporary table with the products table to retrieve the product names.

By understanding these common types of subqueries, you can write more complex and powerful queries to retrieve and manipulate data in meaningful ways.

Complex subqueries are subqueries that involve multiple tables, conditions, and operations. They are used to perform advanced filtering, grouping, and calculation on data, and can be challenging to write and understand. Here are some examples of complex subqueries and how they work:

Subqueries with Multiple Conditions:

A subquery can have multiple conditions, just like any other query. These conditions can be used to filter, group, or calculate data based on various criteria. Here's an example:

In this example, the subquery returns a list of customer IDs for orders with a total greater than 100 and placed after January 1, 2022. The outer query then returns the names and emails of the customers who placed those orders.

Subqueries with Aggregation:

A subquery can also include aggregation functions, such as COUNT, SUM, AVG, MIN, or MAX, to calculate data based on multiple rows. These functions can be used to group, filter, or sort data based on various criteria. Here's an example:

In this example, the subquery calculates the total amount spent by each customer, and the outer query returns the names, emails, and total amount spent for customers who spent more than 1000.

Subqueries with Self-Joins:

A subquery can also involve self-joins, where a table is joined with itself based on a condition. This technique can be used to compare or group data from the same table based on various criteria. Here's an example:

In this example, the subquery involves a self-join between the employees table to retrieve the names of all possible employee pairs. The outer query then joins this result with the orders table to calculate the total sales made by each employee pair between January 1 and December 31, 2022. Finally, the outer query groups the results by employee pairs and sorts them in descending order by total sales.

By using these techniques and combining them with other SQL features such as joins, filters, and sorts, you can write complex subqueries that retrieve, group, and calculate data from multiple tables in a variety of ways.

Subqueries are queries that are nested inside another query. They can be used to retrieve, filter, group, or calculate data from one or more tables based on various conditions. Here are some examples of subqueries and their diagrams:

Subquery with a Single Value Comparison

A subquery with a single value comparison is used to retrieve data from one table based on a condition that compares a single value with a column in another table. Here's an example:

This subquery retrieves a list of employee IDs from the orders table where the order total is greater than 100. The outer query then returns all columns from the employees table for the employees with those IDs.

Diagram:

Subquery with a Multiple Value Comparison

A subquery with a multiple value comparison is used to retrieve data from one table based on a condition that compares multiple values with a column in another table. Here's an example:

This subquery retrieves a list of employee IDs from the orders table where the order date is between January 1 and December 31, 2022. The outer query then returns all columns from the employees table for the employees with those IDs.

Diagram:

Subquery with a Grouped Calculation

A subquery with a grouped calculation is used to retrieve data from one or more tables based on a condition that involves a grouped calculation. Here's an example:

This subquery retrieves a list of employee IDs from the orders table where the sum of order totals for each employee is greater than 10000. The outer query then returns all columns from the employees table for the employees with those IDs.

Diagram:

Subquery with an EXISTS Condition

A subquery with an EXISTS condition is used to retrieve data from one or more tables based on a condition that checks whether a subquery returns any rows. Here's an example

This subquery retrieves a list of orders where the order date is between January 1 and December 31, 2022, and then checks whether there are any corresponding employee IDs in the employees table. The outer query then returns all columns from the employees table for the employees with those IDs.

Diagram:

Subqueries are a powerful feature of SQL that allow you to retrieve data based on complex conditions and calculations. By understanding how subqueries work and how to use them effectively, you can write more sophisticated queries that can provide valuable insights into your data.