Subqueries Quick Start

Quick Start: Using Subqueries in Stoolap

This guide provides a quick introduction to using subqueries in Stoolap SQL statements.

What are Subqueries?

Subqueries are SQL queries nested within another query. They allow you to use the result of one query as input for another, enabling more complex data operations.

Basic IN Subquery Example

Here’s a simple example that demonstrates the power of subqueries:

-- Create sample tables
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total FLOAT
);

-- Insert sample data
INSERT INTO customers VALUES 
    (1, 'Alice', 'USA'),
    (2, 'Bob', 'Canada'),
    (3, 'Charlie', 'USA');

INSERT INTO orders VALUES 
    (1, 1, 100.0),
    (2, 2, 200.0),
    (3, 1, 150.0);

-- Find all orders from US customers using a subquery
SELECT * FROM orders 
WHERE customer_id IN (
    SELECT id FROM customers WHERE country = 'USA'
);
-- Returns orders 1 and 3 (Alice's orders)

EXISTS/NOT EXISTS Example

Check if related records exist without retrieving them:

-- Find customers who have placed at least one order
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Find customers who have never ordered
SELECT name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Scalar Subqueries Example

Use subqueries that return a single value in comparisons:

-- Find orders above average
SELECT * FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- Delete old records below a threshold
DELETE FROM logs
WHERE timestamp < (SELECT MIN(timestamp) FROM logs WHERE priority = 'high');

Common Use Cases

1. Filtering Based on Another Table

-- Delete inactive user data
DELETE FROM user_sessions 
WHERE user_id IN (
    SELECT id FROM users WHERE last_login < DATE('now', '-90 days')
);

2. Bulk Updates Based on Conditions

-- Apply discount to premium category products
UPDATE products 
SET discount = 0.20 
WHERE category_id IN (
    SELECT id FROM categories WHERE tier = 'premium'
);

3. Finding Missing Records

-- Find customers without any orders
SELECT * FROM customers 
WHERE id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);

4. Existence Checks

-- Update product availability
UPDATE products
SET in_stock = false
WHERE NOT EXISTS (
    SELECT 1 FROM inventory WHERE product_id = products.id AND quantity > 0
);

Best Practices

  1. Keep subqueries simple: Complex subqueries can impact performance
  2. Use indexes: Ensure columns used in subquery WHERE clauses are indexed
  3. Consider alternatives: Sometimes a JOIN might be more efficient than a subquery

Current Support

Stoolap currently supports:

  • ✅ IN and NOT IN subqueries
  • ✅ EXISTS and NOT EXISTS operators
  • ✅ Scalar subqueries (returning single values)
  • ✅ Non-correlated subqueries (independent of outer query)
  • ✅ Subqueries in SELECT, UPDATE, and DELETE statements

Coming soon:

  • ❌ Correlated subqueries (referencing outer query)
  • ❌ Subqueries in FROM clause
  • ❌ Subqueries in SET clause of UPDATE
  • ❌ ANY and ALL operators

Next Steps