A subquery, also known as a subselect or inner query, is a query nested within another query in SQL. A subquery can be used within various SQL statements, such as SELECT, INSERT, UPDATE, or DELETE, to retrieve or manipulate data based on the results of the inner query. Subqueries are a fundamental concept in SQL, and they help you perform complex and conditional data retrieval or manipulation operations.

There are two main categories of subqueries based on where they are used within SQL statements:

1. Single-Row Subquery:

   • In a single-row subquery, the inner query returns a single value or row of values, and it is used within a larger query that expects a single value or row.

   • Commonly used within SELECT statements in scenarios where you need to compare a single value with a set of values or perform calculations based on a single value.

   • Example:In this example, the subquery (SELECT AVG(salary) FROM employees) returns a single average salary, which is then used to compare against the salary of each employee.

     sqlCopy code
     SELECT first_name, last_name
     FROM employees
     WHERE salary > (SELECT AVG(salary) FROM employees);
     

2. Multi-Row Subquery:

   • In a multi-row subquery, the inner query returns multiple rows of values, and it is used within a larger query that can handle multiple rows, such as IN, EXISTS, or ANY/ALL comparisons.

   • Commonly used within conditions to check whether a set of values exists in another set, or to perform operations involving multiple rows.

   • Example:In this example, the subquery (SELECT department_id FROM employees WHERE salary > 50000) returns multiple department IDs, and the outer query retrieves department names for those departments.

     sqlCopy code
     SELECT department_name
     FROM departments
     WHERE department_id IN (SELECT department_id FROM employees WHERE salary > 50000);
     

Subqueries can also be categorized based on their purpose:

1. Scalar Subquery:

   • A scalar subquery returns a single value and can be used wherever a single value is expected, such as in a SELECT clause.

   • Typically used for calculations, comparisons, or as a value for a column.

   • Example:In this example, the subquery (SELECT MAX(salary) FROM employees) returns a single maximum salary value for all employees.

     sqlCopy code
     SELECT first_name, last_name, (SELECT MAX(salary) FROM employees) AS max_salary
     FROM employees;
     
     

2. Correlated Subquery:

   • A correlated subquery references columns from the outer query, allowing it to be evaluated once for each row in the outer query.

   • Useful for cases where you need to compare or calculate values based on each row in the outer query.

   • Example:In this example, the subquery (SELECT COUNT(*) FROM employees WHERE department_id = departments.department_id) is correlated with the outer query and counts the number of employees in each department.

     sqlCopy code
     SELECT department_name, (SELECT COUNT(*) FROM employees WHERE department_id = departments.department_id) AS employee_count
     FROM departments;
     

Subqueries are a powerful feature in SQL, allowing you to perform complex data manipulations and conditional operations by nesting queries within each other. They are essential for writing efficient and expressive SQL statements

Single-Row Subquery:

In a single-row subquery, the inner query returns a single value or row of values, and it is used within a larger query that expects a single value or row. Here are some key points and examples:

1. Comparison with a Single Value:

   • Single-row subqueries are commonly used to compare a single value with a set of values in the outer query.

   • Example:In this example, the subquery (SELECT AVG(price) FROM products) returns a single average price, and the outer query retrieves product names with prices higher than the average.

     sqlCopy code
     SELECT product_name
     FROM products
     WHERE price > (SELECT AVG(price) FROM products);
     
     

2. Calculations with a Single Value:

   • You can use single-row subqueries for calculations based on a single value from the subquery.

   • Example:In this example, the subquery (SELECT salary * 1.1 FROM employees WHERE employee_id = 101) calculates a new salary for employee 101, and the outer query lists employees in department 10 with their new salaries.

     sqlCopy code
     SELECT employee_id, salary, (SELECT salary * 1.1 FROM employees WHERE employee_id = 101) AS new_salary
     FROM employees
     WHERE department_id = 10;
     
     

Multi-Row Subquery:

In a multi-row subquery, the inner query returns multiple rows of values, and it is used within a larger query that can handle multiple rows. Here are some key points and examples:

1. IN Operator:

   • Multi-row subqueries are often used with the IN operator to check whether a set of values exists in another set.

   • Example:In this example, the subquery (SELECT product_id FROM order_details WHERE order_id = 1001) returns multiple product IDs, and the outer query retrieves product names for products in order 1001.

     sqlCopy code
     SELECT product_name
     FROM products
     WHERE product_id IN (SELECT product_id FROM order_details WHERE order_id = 1001);
     
     

2. EXISTS Operator:

   • The EXISTS operator is used with multi-row subqueries to check for the existence of rows that meet certain conditions.

   • Example:In this example, the subquery (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id) checks if there are any orders for each customer, and the outer query retrieves customer names for those with orders.

     sqlCopy code
     SELECT customer_name
     FROM customers c
     WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
     
     

Scalar Subquery: