SQLite Non-correlated subqueries

In SQLite, a subquery is a query that is nested within another query. A non-correlated subquery is a type of subquery that does not depend on the outer query for its results. It is executed once for each row in the outer query, and its result is used as a parameter in the outer query.

Non-correlated subqueries can be used in various scenarios, such as in the WHERE clause, HAVING clause, or SELECT clause of a query. They are particularly useful when you need to retrieve data from one table based on conditions that are derived from another table.

Examples

For example, suppose you have two tables: a “Customers” table and an “Orders” table. You want to retrieve the names of all customers who have placed an order in the last 30 days. You can use a non-correlated subquery in the WHERE clause to achieve this:

SELECT name 
FROM Customers 
WHERE customer_id IN 
   (SELECT customer_id 
    FROM Orders 
    WHERE order_date >= date('now', '-30 days'))

In this example, the subquery (SELECT customer_id FROM Orders WHERE order_date >= date(‘now’, ‘-30 days’)) is executed once for each row in the “Customers” table. It returns a list of customer IDs who have placed an order in the last 30 days, and this list is used as a parameter in the outer query’s WHERE clause to filter the results.

Non-correlated subqueries can also be used in the SELECT clause to retrieve aggregate data from a related table. For example, suppose you have a “Customers” table and an “Orders” table, and you want to retrieve the total number of orders placed by each customer:

SELECT name, 
   (SELECT COUNT(*) FROM Orders WHERE customer_id = Customers.customer_id) AS total_orders 
FROM Customers

In this example, the subquery (SELECT COUNT(*) FROM Orders WHERE customer_id = Customers.customer_id) is executed once for each row in the “Customers” table. It returns the total number of orders placed by the current customer, and this value is returned as a column in the outer query’s result set.

Overall, non-correlated subqueries are a powerful feature of SQLite that allow you to perform complex queries on related tables with ease. By understanding how they work and when to use them, you can write efficient and effective SQL queries to retrieve the data you need.