SQLite - Subqueries

SQLite - Subqueries and CTEs

A Subquery, also known as an inner or nested query, is a SELECT statement embedded inside another SQL statement. Subqueries are a powerful tool for performing complex data retrieval and manipulation.

Subqueries are most often used in the WHERE clause to filter data based on the results of another query. They can also be used in SELECT, FROM, INSERT, UPDATE, and DELETE statements.

Rules for Subqueries

• A subquery must be enclosed in parentheses ().
• A subquery in a WHERE clause should typically return a single column. If it might return multiple rows, it must be used with an appropriate operator like IN.
• An ORDER BY clause is generally not allowed in a subquery, as the outer query controls the final ordering. An exception is when used with LIMIT.
• For readability, deeply nested subqueries should be avoided. Consider using JOINs or Common Table Expressions (CTEs) instead.

Subqueries with the SELECT Statement

This is the most common use case. The subquery’s result acts as a filter for the main query.

Example

Consider two tables: CUSTOMERS and ORDERS. We want to find all customers who have placed an order.

CUSTOMERS:
ID          NAME
----------  ----------
1           John Doe
2           Jane Smith
3           Peter Jones

ORDERS:
ORDER_ID    CUSTOMER_ID   AMOUNT
----------  -----------   ------
101         2             50.00
102         2             75.00
103         3             120.00

We can use a subquery with the IN operator to solve this:

SELECT Name FROM CUSTOMERS
WHERE ID IN (SELECT CUSTOMER_ID FROM ORDERS);

The inner query (SELECT CUSTOMER_ID FROM ORDERS) executes first, returning the list (2, 3). The outer query then becomes SELECT Name FROM CUSTOMERS WHERE ID IN (2, 3);, producing:

NAME
----------
Jane Smith
Peter Jones

Subqueries with INSERT, UPDATE, and DELETE

INSERT with Subquery

You can use a subquery to insert the results of a SELECT statement into another table. This is useful for archiving or creating backups.

-- Archive old orders into an ARCHIVED_ORDERS table
INSERT INTO ARCHIVED_ORDERS (ORDER_ID, CUSTOMER_ID, AMOUNT)
  SELECT ORDER_ID, CUSTOMER_ID, AMOUNT
  FROM ORDERS
  WHERE ORDER_DATE < '2023-01-01';

UPDATE with Subquery

A subquery can be used in the WHERE clause of an UPDATE statement to identify which rows to modify.

-- Give a 10% discount to customers who have spent over $1000 total
UPDATE CUSTOMERS
SET DISCOUNT_TIER = 'Gold'
WHERE ID IN (
  SELECT CUSTOMER_ID
  FROM ORDERS
  GROUP BY CUSTOMER_ID
  HAVING SUM(AMOUNT) > 1000
);

DELETE with Subquery

Similarly, a subquery can identify rows for deletion.

-- Delete customers who have not placed any orders
DELETE FROM CUSTOMERS
WHERE ID NOT IN (SELECT CUSTOMER_ID FROM ORDERS);

Modern Approach: Common Table Expressions (CTEs)

For complex queries, nested subqueries can become difficult to read and maintain. SQLite supports Common Table Expressions (CTEs) using the WITH clause. A CTE allows you to define a named, temporary result set that you can reference within your main query. This dramatically improves readability.

CTE Syntax

WITH CteName AS (
  -- Subquery defining the CTE
  SELECT ...
)
-- Main query that uses the CTE
SELECT ... FROM CteName;

Example: Rewriting the UPDATE with a CTE

Let’s rewrite our previous UPDATE example using a CTE. The logic is identical, but the structure is much cleaner.

-- Define a CTE for high-value customers
WITH HighValueCustomers AS (
  SELECT CUSTOMER_ID
  FROM ORDERS
  GROUP BY CUSTOMER_ID
  HAVING SUM(AMOUNT) > 1000
)
-- Use the CTE in the main UPDATE statement
UPDATE CUSTOMERS
SET DISCOUNT_TIER = 'Gold'
WHERE ID IN (SELECT CUSTOMER_ID FROM HighValueCustomers);

CTEs are a best practice for modern SQL, making your queries more organized, readable, and easier to debug.