Subqueries

A subquery is a SELECT nested inside another query, wrapped in parentheses. KSQL supports three shapes:

• IN (SELECT ...) / NOT IN (SELECT ...) — filter a column against the values another query returns.
• Scalar (SELECT ...) — a subquery that returns a single value, usable anywhere a value is allowed (in WHERE, SELECT, expressions).
• Derived tables FROM (SELECT ...) AS x — treat a subquery's result as a table you can select from and join to.

Like every Query Console query, subqueries run against your DataTables. Inner and outer queries can reference the same table or different tables. Nesting is capped at 3 levels deep, covered in the Nesting depth section below.

Subqueries in WHERE: IN and NOT IN

Use IN (SELECT ...) to keep rows whose value appears in another query's result. The subquery must project exactly one column.

-- Customers who have placed an order over $5,000
-- (Orders.Customer is a relation column linking back to Customers)
SELECT Name, Email
FROM Customers
WHERE Name IN (
    SELECT Customer FROM Orders WHERE Amount > 5000
)

NOT IN (SELECT ...) keeps the rows that don't match — the anti-join pattern:

-- Customers who have never placed an order
SELECT Name, Email
FROM Customers
WHERE Name NOT IN (
    SELECT Customer FROM Orders
)

-- Products that have never been ordered
SELECT Name, SKU, Price
FROM Products
WHERE SKU NOT IN (
    SELECT Product FROM Orders
)

One column only. The subquery after IN / NOT IN must select a single column. SELECT * or two columns raises Subquery in IN must project exactly one column.

The list form (IN ('a', 'b', 'c')) and the subquery form are the same operator — see Operators. Reach for the subquery form when the set of values is itself the result of a query rather than a fixed list.

Scalar subqueries

A scalar subquery returns a single value (one row, one column). Wrap it in parentheses and drop it anywhere a value is expected. It must project exactly one column — SELECT * is rejected.

In WHERE — compare against a computed value

-- Products priced above the average price
SELECT Name, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)
ORDER BY Price DESC

-- Deals larger than the biggest deal closed last quarter
SELECT Name, Amount, Owner
FROM Deals
WHERE Amount > (
    SELECT MAX(Amount) FROM Deals WHERE ClosedAt < LOOKBACK(3, 'months')
)

The inner query runs first and produces one number; the outer WHERE then compares each row against it.

In SELECT — add a computed column

A scalar subquery in the SELECT list becomes a column on every result row:

-- Every product, plus how far its price sits above the catalog average
SELECT Name,
       Price,
       Price - (SELECT AVG(Price) FROM Products) AS above_avg
FROM Products
ORDER BY above_avg DESC

The subquery above is uncorrelated — it doesn't reference the outer row, so it computes the same value for every row.

A correlated scalar subquery references a column from the outer query, so it recomputes per row:

-- Each customer with a live count of their orders
SELECT Name,
       (SELECT COUNT(*) FROM Orders WHERE Orders.Customer = Customers.Name) AS order_count
FROM Customers
ORDER BY order_count DESC

A JOIN + GROUP BY often produces the same result as a correlated subquery and can be easier to read across several aggregates — see Joins and Grouping. Use whichever expresses your intent most clearly.

Derived tables (subquery in FROM)

You can select FROM a subquery as if it were a table. A derived table requires an alias so its columns can be referenced.

-- Rank owners by total pipeline, then keep the top tier
SELECT Owner, total
FROM (
    SELECT Owner, SUM(Amount) AS total
    FROM Deals
    GROUP BY Owner
) AS pipeline
WHERE total > 250000
ORDER BY total DESC

Omit the alias and you get Derived tables in FROM require an alias: FROM (SELECT ...) AS x. Derived tables can also be joined like any other table.

Nesting depth (max 3 levels)

Subqueries can nest, but only 3 levels deep. The outer query plus three nested subqueries is the maximum:

-- 3 levels — allowed
SELECT Name FROM Customers
WHERE Name IN (                                 -- level 1
    SELECT Customer FROM Orders
    WHERE Product IN (                          -- level 2
        SELECT SKU FROM Products
        WHERE Category IN (                     -- level 3
            SELECT Name FROM Categories WHERE Featured = 1
        )
    )
)

Add a fourth level and the parser rejects it before the query runs:

-- 4 levels — rejected
SELECT Name FROM Customers
WHERE Name IN (                                 -- level 1
    SELECT Customer FROM Orders
    WHERE Product IN (                          -- level 2
        SELECT SKU FROM Products
        WHERE Category IN (                     -- level 3
            SELECT Name FROM Categories
            WHERE Region IN (                   -- level 4 → error
                SELECT Name FROM Regions WHERE Active = 1
            )
        )
    )
)

Subqueries cannot be nested more than 3 levels deep

Three levels covers essentially every real reporting query. If you find yourself needing more, splitting into a saved query or a derived table (shown above) usually reads better anyway.

Recipes

-- Customers who have an order over $10,000
SELECT Name, Email
FROM Customers
WHERE Name IN (
    SELECT Customer FROM Orders WHERE Amount > 10000
)

-- Products priced above the average
SELECT Name, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)
ORDER BY Price DESC

-- Customers with no orders in the last 90 days (win-back list)
SELECT Name, Email
FROM Customers
WHERE Name NOT IN (
    SELECT Customer FROM Orders WHERE OrderDate > LOOKBACK(90, 'days')
)

-- All deals owned by whoever owns the single biggest deal
-- (Owner is a people column; the scalar subquery returns one person)
SELECT Name, Amount, Owner
FROM Deals
WHERE Owner = (
    SELECT Owner FROM Deals ORDER BY Amount DESC LIMIT 1
)

-- Employees whose salary beats their department average (correlated)
SELECT Name, Department, Salary
FROM Employees e
WHERE Salary > (
    SELECT AVG(Salary) FROM Employees WHERE Department = e.Department
)

-- Open issues on products that are currently out of stock
SELECT Title, Product
FROM Issues
WHERE Status = 'open'
  AND Product IN (
      SELECT SKU FROM Products WHERE StockLevel = 0
  )

Errors

Next

• Joins → — often a clearer alternative to a subquery
• Grouping & Aggregation → — GROUP BY / HAVING for the aggregates subqueries compare against
• Recipes → — more practical query patterns