Using Subqueries

This guide provides a quick tutorial on using subqueries in Oxibase 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
-- Returns orders 1 and 3 (Alice's orders)
SELECT * FROM orders 
WHERE customer_id IN (
    SELECT id FROM customers WHERE country = 'USA'
);

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:

-- Create logs table
CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    message TEXT,
    timestamp TEXT,
    priority TEXT
);

-- Insert sample data
INSERT INTO logs VALUES
    (1, 'System error', '2023-01-01 10:00:00', 'high'),
    (2, 'User action', '2023-01-15 14:30:00', 'low'),
    (3, 'Warning', '2023-02-01 09:15:00', 'medium');

-- 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

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

CREATE TABLE user_sessions (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    session_data TEXT
);

-- Insert sample data
INSERT INTO users VALUES
    (1, 'Alice', '2023-10-01'),
    (2, 'Bob', '2023-12-01'),
    (3, 'Charlie', '2023-09-15');

INSERT INTO user_sessions VALUES
    (1, 1, 'data1'),
    (2, 3, 'data2');

-- 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

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

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    category_id INTEGER,
    discount FLOAT
);

-- Insert sample data
INSERT INTO categories VALUES
    (1, 'Electronics', 'premium'),
    (2, 'Books', 'standard');

INSERT INTO products VALUES
    (1, 'Laptop', 1, 0.0),
    (2, 'Novel', 2, 0.0),
    (3, 'Phone', 1, 0.0);

-- 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

-- Alter products table
ALTER TABLE products ADD COLUMN in_stock BOOLEAN DEFAULT true;

-- Create sample table
CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    product_id INTEGER,
    quantity INTEGER
);

-- Insert sample data
INSERT INTO inventory VALUES
    (1, 1, 10),
    (2, 2, 0),
    (3, 3, 5);

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

5. Correlated Subqueries

-- Create sample table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary FLOAT
);

-- Insert sample data
INSERT INTO employees VALUES
    (1, 'Alice', 'Engineering', 80000.0),
    (2, 'Bob', 'Engineering', 75000.0),
    (3, 'Charlie', 'Sales', 70000.0),
    (4, 'David', 'Sales', 65000.0);

-- Find employees earning above their department average
SELECT name, department, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department
);

-- Get each customer's total order amount
SELECT
    c.name,
    (SELECT SUM(total) FROM orders o WHERE o.customer_id = c.id) as total_spent
FROM customers c;

6. Derived Tables (Subqueries in FROM)

-- Join with aggregated data
SELECT c.name, stats.order_count, stats.total_spent
FROM customers c
JOIN (
    SELECT customer_id, COUNT(*) as order_count, SUM(total) as total_spent
    FROM orders
    GROUP BY customer_id
) AS stats ON c.id = stats.customer_id;

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

Supported Features

Oxibase fully supports:

• ✅ IN and NOT IN subqueries
• ✅ EXISTS and NOT EXISTS operators
• ✅ Scalar subqueries (returning single values)
• ✅ Correlated subqueries (referencing outer query)
• ✅ Derived tables (subqueries in FROM clause)
• ✅ Subqueries in SELECT, UPDATE, and DELETE statements
• ✅ Subqueries in UPDATE SET clause
• ✅ ANY/SOME and ALL operators

Next Steps

• Read the full Subqueries documentation
• Learn about JOIN operations as an alternative
• Explore SQL Commands reference