Subqueries, also known as nested queries, are SQL statements within other SQL statements. These queries are used to retrieve data from one table, and then use that data to perform a second query on another table. The results of the second query can then be used as part of the first query’s results.

There are two main types of subqueries: correlated and non-correlated. In a non-correlated subquery, the inner query does not depend on the outer query. It can be run independently of the outer query and is executed first. The result of the inner query is then used to perform the outer query.

In a correlated subquery, the inner query is dependent on the outer query. The inner query is executed once for each row of the outer query. This means that the inner query is executed multiple times, once for each row returned by the outer query.

Subqueries can be used in a variety of ways, such as:

1. Filtering: A subquery can be used to filter the results of a query. For example, if you want to retrieve all orders for a specific customer, you can use a subquery to get the customer ID and then use that ID to filter the order table.
2. Joining: A subquery can be used to join tables. For example, if you want to retrieve all orders for a specific customer and the customer’s address, you can use a subquery to join the customer and address tables.
3. Calculating: A subquery can be used to perform calculations. For example, if you want to retrieve the total number of orders for each customer, you can use a subquery to count the number of orders for each customer.

Subqueries have several advantages, including:

1. Flexibility: Subqueries can be used to perform complex queries that would be difficult or impossible to perform with a single query.
2. Simplification: Subqueries can simplify complex queries by breaking them down into smaller, more manageable parts.
3. Performance: Subqueries can sometimes improve performance by allowing the database to execute smaller queries that are more efficient.

However, subqueries also have some disadvantages, including:

1. Performance: Subqueries can sometimes be slower than other methods of achieving the same result.
2. Complexity: Subqueries can be difficult to read and understand, especially for those who are new to SQL.
3. Maintenance: Subqueries can make SQL code more difficult to maintain, as changes to the outer query or the subquery can have a cascading effect on other parts of the code.

In summary, subqueries are a powerful tool in SQL that can be used to perform complex queries, join tables, and perform calculations. They have several advantages, but also come with some drawbacks that should be considered before using them in SQL code.

Examples

1. Find all employees whose salary is higher than the average salary of their department:

SQL

SELECT *
FROM employees
WHERE salary > (
   SELECT AVG(salary)
   FROM employees
   WHERE department = employees.department
);

2. Find all customers who have placed an order for a product that is out of stock:

SQL

SELECT *
FROM customers
WHERE customer_id IN (
   SELECT customer_id
   FROM orders
   WHERE product_id IN (
      SELECT product_id
      FROM products
      WHERE stock = 0
   )
);

3. Find the name of the department with the highest average salary:

SQL

SELECT department
FROM employees
GROUP BY department
HAVING AVG(salary) = (
   SELECT MAX(avg_salary)
   FROM (
      SELECT AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department
   ) AS temp
);

4. Find all employees who have a higher salary than their manager:

SQL

SELECT *
FROM employees
WHERE salary > (
   SELECT salary
   FROM employees AS managers
   WHERE managers.employee_id = employees.manager_id
);