Subqueries, also known as nested queries, are SQL queries embedded within another SQL query. They are used to retrieve data from one table based on the result of another query. Subqueries can be powerful tools for working with multiple tables, allowing you to perform complex operations and filter data effectively. Here are some real-time examples of subqueries with detailed explanations:

Example 1: Finding Customers with High Sales

Suppose you have two tables: customers and orders. You want to find customers who have placed orders with a total amount greater than $1,000.

sqlCopy code
SELECT customer_name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  HAVING SUM(order_amount) > 1000
);

• In this example, the inner subquery retrieves customer IDs from the orders table, grouped by customer ID, and calculates the total order amount for each customer.
• The outer query then selects customer names from the customers table where the customer ID is found in the result of the subquery.
• The subquery helps filter customers based on a condition that involves data from multiple tables.

Example 2: Finding Employees with Higher Salaries Than Managers

Suppose you have an employees table where each employee has a manager (represented by the manager_id column). You want to find employees who have a salary higher than their respective managers.

sqlCopy code
SELECT employee_name
FROM employees
WHERE salary > (
  SELECT salary
  FROM employees AS managers
  WHERE employees.manager_id = managers.employee_id
);

• In this example, the subquery retrieves the salary of each employee's manager. It does this by joining the employees table with itself using aliases to distinguish between the employee and the manager.
• The outer query then selects employee names from the employees table where the salary is greater than the result of the subquery.
• This subquery helps compare data within the same table to make decisions based on relationships between rows.

Example 3: Finding Most Recent Orders for Each Customer

Suppose you have a customers table and an orders table, and you want to find the most recent order for each customer.

sqlCopy code
SELECT customer_name, order_date
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
WHERE (orders.customer_id, order_date) IN (
  SELECT customer_id, MAX(order_date)
  FROM orders
  GROUP BY customer_id
);

• In this example, the subquery finds the maximum (most recent) order date for each customer by grouping orders by customer ID.
• The outer query joins the customers and orders tables using the customer ID as the join condition.
• The subquery helps identify the latest order date for each customer, allowing you to retrieve the corresponding customer name.

Example 4: Using Subquery in the WHERE Clause with NOT IN