SQLite Correlated subqueries

In SQLite, a correlated subquery is a subquery that references one or more columns from the outer query. This allows the subquery to be executed once for each row of the outer query, resulting in a more precise and context-specific result set.

Examples

Correlated subqueries in SQLite are useful when you need to filter or aggregate data from a table based on values from another table or a column within the same table. For example, you might use a correlated subquery to find all customers who have made a purchase in the last 30 days:

SELECT *
FROM customers
WHERE EXISTS (
  SELECT *
  FROM orders
  WHERE orders.customer_id = customers.id
    AND orders.date > DATE('now', '-30 days')
);

In this example, the subquery references the customers.id column from the outer query and the orders.customer_id and orders.date columns from the orders table. For each row in the customers table, the subquery is executed to determine whether there are any orders for that customer within the last 30 days. If there are, the customer is included in the result set.

Correlated subqueries can also be used to perform aggregate calculations based on values from another table or column. For example, you might use a correlated subquery to find the average order amount for each customer:

SELECT id, name, (
  SELECT AVG(amount)
  FROM orders
  WHERE orders.customer_id = customers.id
) AS avg_order_amount
FROM customers;

In this example, the subquery calculates the average order amount for each customer by referencing the customers.id column from the outer query and the orders.customer_id and orders.amount columns from the orders table. The result is returned as a new column called avg_order_amount.

Correlated subqueries in SQLite can be a powerful tool for filtering and aggregating data based on values from other tables or columns. However, they can also be slow and resource-intensive, especially when working with large data sets. It’s important to optimize your queries and use appropriate indexes to improve performance.