SQL - Use Subqueries for Complex Data Retrieval

SQL subqueries are powerful tools for retrieving complex and specific data by nesting one query within another. Use subqueries to break down complex tasks into smaller, more manageable steps, making your SQL code more readable and maintainable.

Example:
Suppose you have a database with tables "orders" and "order_items," and you want to find the total quantity of a specific product sold across all orders.

Without Subquery (Complex):

SELECT product_id, SUM(quantity) AS total_quantity
FROM order_items
WHERE product_id = 123
GROUP BY product_id;

With Subquery (Simplified and Readable):

SELECT product_id, (
  SELECT SUM(quantity)
  FROM order_items
  WHERE product_id = 123
) AS total_quantity;

In this example, the subquery calculates the total quantity for the specific product (product_id = 123), and the main query retrieves the product_id along with the subquery result. This approach makes the SQL code more concise and easier to understand, especially when dealing with complex aggregations or filtering conditions.

When using subqueries, keep in mind that they can impact query performance, so it's essential to optimize them and consider alternatives like JOINs or common table expressions (CTEs) when appropriate.