A subquery, also known as a nested query or inner query, is a query that is embedded within another query. It’s a powerful feature in SQL that allows you to retrieve data from one or more tables based on the results of another query. Subqueries are often used in situations where you need to perform a query within a query to filter or manipulate data.

There are two main types of subqueries: correlated and non-correlated.

Non-Correlated Subquery: A non-correlated subquery is a subquery that can be executed independently of the outer query. It’s executed once and its result is used by the outer query to perform filtering or comparison.

SELECT product_name
FROM products
WHERE product_id IN (SELECT product_id FROM orders WHERE order_date = '2023-07-01');

In this example, the subquery (SELECT product_id FROM orders WHERE order_date = '2023-07-01') retrieves a list of product_id values from the orders table. The outer query then uses this list to retrieve the corresponding product_name values from the products table.

Non-Correlated Subquery from Todo-Project:

SELECT username FROM users WHERE id IN (SELECT user_id FROM todos WHERE inserted_at = '2023-08-11 14:30:04');

Correlated Subquery: A correlated subquery is a subquery that depends on the outer query for its values. It’s executed for each row processed by the outer query, resulting in potentially multiple executions of the subquery.

SELECT employee_name
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

In this example, the subquery (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) calculates the average salary for each department. The outer query then compares the salary of each employee with the average salary of their department.

Correlated Subquery from Todo-Project:-

SELECT username
FROM users
WHERE id > (SELECT AVG(id) FROM users WHERE name = "sammy");

Subqueries are versatile and can be used in various scenarios, such as filtering, comparison, calculations, and more. They are particularly useful when you need to perform complex queries involving multiple conditions or aggregations, making your SQL queries more dynamic and adaptable.