SQL Deep Dive: JOINs, CTEs, and Subqueries

Guide March 31, 2026 · 10 min read

CSharpDB ships a full SQL engine — not a SQL-like query language, but proper SQL with JOINs, aggregates, CTEs, subqueries, and set operations. This post walks through the advanced features with practical examples.

Setup: Sample Schema

We'll use a simple e-commerce schema throughout this guide:

await db.ExecuteAsync(@"
    CREATE TABLE Customers (
        Id    INTEGER PRIMARY KEY,
        Name  TEXT NOT NULL,
        Email TEXT NOT NULL
    );
    CREATE TABLE Orders (
        Id         INTEGER PRIMARY KEY,
        CustomerId INTEGER REFERENCES Customers(Id),
        Total      REAL NOT NULL,
        CreatedAt  TEXT NOT NULL
    );
    CREATE TABLE OrderItems (
        Id        INTEGER PRIMARY KEY,
        OrderId   INTEGER REFERENCES Orders(Id),
        Product   TEXT NOT NULL,
        Quantity  INTEGER NOT NULL,
        UnitPrice REAL NOT NULL
    )");

JOINs

CSharpDB supports INNER, LEFT, RIGHT, and CROSS JOINs:

await using var results = await db.ExecuteAsync(@"
    SELECT c.Name, o.Total, o.CreatedAt
    FROM Customers c
    INNER JOIN Orders o ON o.CustomerId = c.Id
    WHERE o.Total > 100
    ORDER BY o.Total DESC");

LEFT JOIN for Optional Relationships

Find customers who may or may not have orders:

await using var results = await db.ExecuteAsync(@"
    SELECT c.Name, COUNT(o.Id) AS OrderCount
    FROM Customers c
    LEFT JOIN Orders o ON o.CustomerId = c.Id
    GROUP BY c.Id, c.Name
    ORDER BY OrderCount DESC");

Multi-Table JOINs

Join three tables to get full order details:

await using var results = await db.ExecuteAsync(@"
    SELECT c.Name, oi.Product, oi.Quantity, oi.UnitPrice,
           oi.Quantity * oi.UnitPrice AS LineTotal
    FROM Customers c
    INNER JOIN Orders o ON o.CustomerId = c.Id
    INNER JOIN OrderItems oi ON oi.OrderId = o.Id
    ORDER BY c.Name, oi.Product");

Aggregates and GROUP BY

Standard aggregate functions — COUNT, SUM, AVG, MIN, MAX — work with GROUP BY and HAVING:

await using var results = await db.ExecuteAsync(@"
    SELECT c.Name,
           COUNT(o.Id)  AS TotalOrders,
           SUM(o.Total) AS TotalSpent,
           AVG(o.Total) AS AvgOrder
    FROM Customers c
    INNER JOIN Orders o ON o.CustomerId = c.Id
    GROUP BY c.Id, c.Name
    HAVING SUM(o.Total) > 500
    ORDER BY TotalSpent DESC");

Common Table Expressions (CTEs)

CTEs let you break complex queries into readable, named steps:

await using var results = await db.ExecuteAsync(@"
    WITH CustomerTotals AS (
        SELECT CustomerId,
               SUM(Total) AS LifetimeSpend,
               COUNT(*)   AS OrderCount
        FROM Orders
        GROUP BY CustomerId
    )
    SELECT c.Name, ct.LifetimeSpend, ct.OrderCount
    FROM Customers c
    INNER JOIN CustomerTotals ct ON ct.CustomerId = c.Id
    WHERE ct.LifetimeSpend > 1000
    ORDER BY ct.LifetimeSpend DESC");

Subqueries

CSharpDB supports scalar subqueries, IN subqueries, and EXISTS — including correlated subqueries:

Scalar Subquery

await using var results = await db.ExecuteAsync(@"
    SELECT Name, Email,
           (SELECT COUNT(*) FROM Orders o WHERE o.CustomerId = c.Id) AS OrderCount
    FROM Customers c");

IN Subquery

-- Customers who ordered products over $50
await using var results = await db.ExecuteAsync(@"
    SELECT Name FROM Customers
    WHERE Id IN (
        SELECT DISTINCT CustomerId FROM Orders WHERE Total > 50
    )");

EXISTS Subquery

-- Customers with at least one order
await using var results = await db.ExecuteAsync(@"
    SELECT Name FROM Customers c
    WHERE EXISTS (
        SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id
    )");

Set Operations

Combine results from multiple queries with UNION, INTERSECT, and EXCEPT:

-- All product names from orders, deduplicated
await using var results = await db.ExecuteAsync(@"
    SELECT Product FROM OrderItems WHERE UnitPrice > 50
    UNION
    SELECT Product FROM OrderItems WHERE Quantity > 10");

DISTINCT

Deduplicate rows in any SELECT, including with aggregates:

-- Unique products ordered
await using var results = await db.ExecuteAsync(
    "SELECT DISTINCT Product FROM OrderItems ORDER BY Product");

-- Count of distinct customers who placed orders
await using var count = await db.ExecuteAsync(
    "SELECT COUNT(DISTINCT CustomerId) FROM Orders");

Views and Triggers

Encapsulate complex queries as views and react to data changes with triggers:

-- Create a reusable view
await db.ExecuteAsync(@"
    CREATE VIEW CustomerSummary AS
    SELECT c.Id, c.Name, c.Email,
           COUNT(o.Id) AS OrderCount,
           COALESCE(SUM(o.Total), 0) AS TotalSpent
    FROM Customers c
    LEFT JOIN Orders o ON o.CustomerId = c.Id
    GROUP BY c.Id, c.Name, c.Email");

-- Query the view like a table
await using var results = await db.ExecuteAsync(
    "SELECT * FROM CustomerSummary WHERE TotalSpent > 500");

Performance Tips

Create indexes on JOIN columns. CREATE INDEX idx_orders_customer ON Orders(CustomerId) dramatically speeds up JOINs.
Use composite indexes for multi-column filters. A single composite index can cover both the WHERE and ORDER BY.
Run ANALYZE periodically. CSharpDB uses table statistics to pick better query plans.
Use parameterized queries. The prepared statement cache avoids re-parsing identical SQL.

← Working with Collections All posts →