UNION and subqueries

Put the rows of two queries together, and use one query inside another.

• Category: Querying
• Keywords: UNION, UNION ALL, ALL, subquery, sub-query, nested query, inner query, EXISTS, NOT EXISTS, IN (SELECT), NOT IN (SELECT), scalar subquery, derived table, correlated subquery, INTERSECT, EXCEPT, UNION DISTINCT, combine results
• See also: SELECT (from), WHERE (in-list), Joins (join), Joins (left-join), GROUP BY and HAVING (group-by), ORDER BY, LIMIT, OFFSET (order-by), Views

A subquery is a SELECT inside another statement, always in ( ). UNION ALL puts the rows of two or more SELECTs one after the other.

• A subquery that uses no column of the outer query runs once, before the outer query. Then KDB uses the result like a fixed value, a fixed list or a small table.
• A subquery may use columns of the outer query (a "correlated" subquery): see Correlated subqueries.
• Subqueries work in SELECT, INSERT, UPDATE and DELETE (also inside CREATE VIEW and EXPLAIN).
• Subqueries and views can be nested at most 16 levels deep.

Example: Brokers who bought or sold TELCO: a subquery with UNION ALL inside (sample data: sample)

SELECT id, name
FROM brokers
WHERE id IN (SELECT buyer FROM trade_log WHERE symbol = 'TELCO'
             UNION ALL
             SELECT seller FROM trade_log WHERE symbol = 'TELCO')
ORDER BY name;

Result:

(5 rows)

UNION ALL

Syntax:

SELECT ... [FROM ... WHERE ... GROUP BY ...]
UNION ALL
SELECT ... [FROM ... WHERE ... GROUP BY ...]
[UNION ALL SELECT ...] ...
[ORDER BY result_column | position [ASC | DESC], ...]
[LIMIT n [OFFSET m]]

Runs each SELECT and puts all their rows into one result. Rows that are the same are kept (that is the meaning of ALL).

• Returns: the rows of the first part, then the rows of the second part, and so on. The column names come from the first part.
• NULL: NULL values are kept like any other value.
• Notes:
  • Every part must have the same number of columns.
  • KDB does not check that the column types match. Put the same kind of value in the same position, or the column mixes numbers and text.
  • ORDER BY after UNION ALL can only use a result column name, an alias of the first part, or a position (ORDER BY 2). An expression like ORDER BY amount * 2 is an error.
  • One part cannot have its own ORDER BY or LIMIT. Wrap that part in FROM (SELECT ... LIMIT n) t (example below).
  • UNION (without ALL) removes rows that are the same. INTERSECT keeps rows found in both parts, EXCEPT rows of the first part that are not in the second. With ALL they count copies. INTERSECT goes before UNION and EXCEPT.

Example: Broker 58: what it bought and sold, per symbol (sample data: sample)

SELECT symbol, 'buy' AS side, sum(quantity) AS shares FROM trade_log WHERE buyer = 58 GROUP BY symbol
UNION ALL
SELECT symbol, 'sell' AS side, sum(quantity) AS shares FROM trade_log WHERE seller = 58 GROUP BY symbol
ORDER BY symbol, side;

Result:

(6 rows)

Example: Turnover of each broker on one day (bought + sold) (sample data: sample)

SELECT broker, sum(amount) AS turnover, count(*) AS trades
FROM (
  SELECT buyer AS broker, amount FROM trade_log WHERE date IN '2026-09-10'
  UNION ALL
  SELECT seller AS broker, amount FROM trade_log WHERE date IN '2026-09-10'
) t
GROUP BY broker
ORDER BY turnover DESC;

Result:

(5 rows)

A GROUP BY over a UNION ALL like the one above is grouped part by part: each part sums its own rows in the column engine and the outer query adds up one row per broker and part, so it costs about as much as the two parts on their own. EXPLAIN shows it as aggregates pushed into the 2 UNION ALL parts. See GROUP BY with many groups for what it covers.

Only sum, count, min and max can be added up part by part. With avg, first, last or count(DISTINCT ...) the parts are not grouped first, but their rows still go straight into the groups without being kept: EXPLAIN says read part by part into the grouping (not kept).

Example: EXPLAIN: grouped part by part, and read part by part (sample data: sample)

EXPLAIN SELECT broker, sum(amount) AS turnover, count(*) AS trades
FROM (SELECT buyer AS broker, amount FROM trade_log WHERE date IN '2026-09-10'
      UNION ALL
      SELECT seller AS broker, amount FROM trade_log WHERE date IN '2026-09-10') t
GROUP BY broker;
EXPLAIN SELECT broker, avg(amount) AS avg_amount
FROM (SELECT buyer AS broker, amount FROM trade_log WHERE date IN '2026-09-10'
      UNION ALL
      SELECT seller AS broker, amount FROM trade_log WHERE date IN '2026-09-10') t
GROUP BY broker;

Result:

EXPLAIN SELECT broker, sum(amount) AS turnover, count(*) AS trades FROM (SELECT buyer AS broker, amo

(4 rows)

EXPLAIN SELECT broker, avg(amount) AS avg_amount FROM (SELECT buyer AS broker, amount FROM trade_log

(3 rows)

Example: One activity list from news and forum posts, newest first

Setup:

CREATE TABLE news (id LONG PRIMARY KEY, title STRING, published_at TIMESTAMP);
CREATE TABLE forum_posts (id LONG PRIMARY KEY, title STRING, created_at TIMESTAMP);
INSERT INTO news VALUES
  (1, 'Market index gains 12 points', '2026-09-10T15:10:00+05:45'),
  (2, 'CITY announces bonus shares', '2026-09-09T16:00:00+05:45');
INSERT INTO forum_posts VALUES
  (10, 'Is VOLT a good buy now?', '2026-09-10T13:20:00+05:45'),
  (11, 'Hydro stocks after the monsoon', '2026-09-08T09:00:00+05:45');

SELECT 'news' AS kind, id, title, published_at AS at FROM news
UNION ALL
SELECT 'forum', id, title, created_at FROM forum_posts
ORDER BY at DESC
LIMIT 3;

Result:

(3 rows)

Example: Different values in the same column: KDB does not stop you

SELECT 1 AS n, 'a' AS t
UNION ALL
SELECT 'x', 2;

Result:

(2 rows)

Example: LIMIT for one part: wrap it in FROM ( ) (sample data: sample)

SELECT * FROM (SELECT transaction_id, symbol, amount FROM trade_log WHERE date IN '2026-09-09' ORDER BY amount DESC LIMIT 1) a
UNION ALL
SELECT * FROM (SELECT transaction_id, symbol, amount FROM trade_log WHERE date IN '2026-09-10' ORDER BY amount DESC LIMIT 1) b;

Result:

(2 rows)

Example: Symbols that traded and also have a daily candle (no copies) (sample data: sample)

SELECT symbol FROM trade_log
INTERSECT
SELECT symbol FROM ochl_day
ORDER BY 1;

Result:

(3 rows)

Example: Every part needs the same number of columns (sample data: sample; shows an error)

SELECT symbol FROM trade_log
UNION ALL
SELECT symbol, close FROM ochl_day;

Result:

Error: UNION ALL part 2 has 2 columns, the first part has 1

Example: ORDER BY after UNION ALL needs a result column (sample data: sample; shows an error)

SELECT symbol, quantity FROM trade_log
UNION ALL
SELECT symbol, volume FROM ochl_day
ORDER BY quantity * 2;

Result:

Error: line 4, column 19: ORDER BY after UNION ALL must name a result column or position

Example: A part cannot have its own LIMIT (sample data: sample; shows an error)

SELECT symbol FROM securities LIMIT 1
UNION ALL
SELECT 'X';

Result:

Error: line 2, column 1: expected OFFSET, ';' or the end of the input but found 'UNION'

(SELECT ...) as a value

Syntax:

(SELECT expression FROM ... [WHERE ...] [LIMIT 1])

A subquery in ( ) where a value is expected: in the SELECT list, in WHERE, in HAVING or in a join's ON. It must return 0 or 1 row.

• Returns: the value of the first column of that row.
• NULL: when the subquery returns no row, the value is NULL.
• Notes:
  • More than one row is an error: subquery returned 3 rows; a value needs exactly one (add LIMIT 1?).
  • With more than one column, KDB uses the first column and does not complain. Select only one column.
  • The subquery runs once, so every row of the outer query gets the same value.

Example: Each close next to the best close of the day (sample data: sample)

SELECT symbol, close,
       (SELECT max(close) FROM ochl_day WHERE time IN '2026-09-10') AS best_close,
       round(close / (SELECT max(close) FROM ochl_day WHERE time IN '2026-09-10') * 100, 1) AS pct_of_best
FROM ochl_day
WHERE time IN '2026-09-10';

Result:

(3 rows)

Example: The trade at the highest CITY rate (sample data: sample)

SELECT date, transaction_id, buyer, seller, quantity, rate
FROM trade_log
WHERE symbol = 'CITY'
  AND rate = (SELECT max(rate) FROM trade_log WHERE symbol = 'CITY');

Result:

(1 row)

Example: The last trading day in the table, without typing the date (sample data: sample)

SELECT time, symbol, close
FROM ochl_day
WHERE time = (SELECT max(time) FROM ochl_day)
ORDER BY close DESC;

Result:

(3 rows)

Example: Symbols with more trades than the average symbol (sample data: sample)

SELECT symbol, count(*) AS trades
FROM trade_log
GROUP BY symbol
HAVING count(*) > (SELECT avg(n) FROM (SELECT symbol, count(*) AS n FROM trade_log GROUP BY symbol) t);

Result:

(2 rows)

Example: No row gives NULL (sample data: sample)

SELECT (SELECT max(close) FROM ochl_day WHERE symbol = 'TELCO') AS telco_best_close;

Result:

(1 row)

Example: More than one row is an error (sample data: sample; shows an error)

SELECT symbol, (SELECT close FROM ochl_day WHERE time IN '2026-09-10') AS close
FROM securities;

Result:

Error: line 1, column 16: subquery returned 3 rows; a value needs exactly one (add LIMIT 1?)

IN (SELECT ...), NOT IN (SELECT ...)

Syntax:

expression IN (SELECT column FROM ... [WHERE ...])
expression NOT IN (SELECT column FROM ... [WHERE ...])

True when the value is (or is not) one of the values the subquery returns. It works like IN ('a', 'b', ...) with the list made by the subquery (see IN).

• Returns: TRUE or FALSE (or NULL, see below).
• NULL: a NULL on the left gives NULL. When the value is not found and the list has a NULL, the result is NULL too, so NOT IN keeps no rows. Add WHERE column IS NOT NULL inside the subquery.
• Notes:
  • An empty subquery gives an empty list: IN is false for every row, NOT IN is true.
  • With more than one column, KDB uses the first column. To match two columns at once, write (a, b) IN (SELECT x, y FROM ...) (see row IN).
  • The subquery can use UNION ALL, GROUP BY, ORDER BY and LIMIT.
  • For a symbol column, IN (SELECT ...) still lets KDB read only the rows of those symbols (see EXPLAIN).
  • In UPDATE and DELETE too: in SET and in the WHERE.

Example: Securities with a trade of 1000 shares or more (sample data: sample)

SELECT symbol, name
FROM securities
WHERE symbol IN (SELECT symbol FROM trade_log WHERE quantity >= 1000);

Result:

(2 rows)

Example: Securities with no daily candles yet (sample data: sample)

SELECT symbol, name, sector
FROM securities
WHERE symbol NOT IN (SELECT symbol FROM ochl_day);

Result:

(2 rows)

Example: Candles of the 3 symbols with the biggest turnover (TELCO has no candles) (sample data: sample)

SELECT time, symbol, close, volume
FROM ochl_day
WHERE time IN '2026-09-10'
  AND symbol IN (SELECT symbol
                 FROM (SELECT symbol, sum(amount) AS turnover FROM trade_log
                       WHERE date IN '2026-09-10' GROUP BY symbol
                       ORDER BY turnover DESC LIMIT 3) top3);

Result:

(2 rows)

Example: Users who wrote a post, for an app

Setup:

CREATE TABLE app_users (id LONG PRIMARY KEY, name STRING, banned BOOL);
CREATE TABLE posts (id LONG PRIMARY KEY, user_id LONG, title STRING);
INSERT INTO app_users VALUES (1, 'sita', FALSE), (2, 'ram', FALSE), (3, 'hari', TRUE);
INSERT INTO posts VALUES (100, 1, 'COAST results'), (101, 3, 'Buy now!!!'), (102, 1, 'Hydro outlook');

SELECT id, name FROM app_users WHERE id IN (SELECT user_id FROM posts) ORDER BY id;
SELECT id, name FROM app_users WHERE id NOT IN (SELECT user_id FROM posts);

Result:

SELECT id, name FROM app_users WHERE id IN (SELECT user_id FROM posts) ORDER BY id

(2 rows)

SELECT id, name FROM app_users WHERE id NOT IN (SELECT user_id FROM posts)

(1 row)

Example: A NULL in the list: NOT IN keeps nothing

Setup:

CREATE TABLE app_users (id LONG PRIMARY KEY, name STRING);
CREATE TABLE posts (id LONG PRIMARY KEY, user_id LONG);
INSERT INTO app_users VALUES (1, 'sita'), (2, 'ram');
INSERT INTO posts VALUES (100, 1), (101, NULL);

SELECT name FROM app_users WHERE id NOT IN (SELECT user_id FROM posts);
SELECT name FROM app_users WHERE id NOT IN (SELECT user_id FROM posts WHERE user_id IS NOT NULL);

Result:

SELECT name FROM app_users WHERE id NOT IN (SELECT user_id FROM posts)

(0 rows)

SELECT name FROM app_users WHERE id NOT IN (SELECT user_id FROM posts WHERE user_id IS NOT NULL)

(1 row)

Example: Clean up posts of users who are gone

Setup:

CREATE TABLE app_users (id LONG PRIMARY KEY, name STRING);
CREATE TABLE posts (id LONG PRIMARY KEY, user_id LONG);
INSERT INTO app_users VALUES (1, 'sita');
INSERT INTO posts VALUES (100, 1), (101, 9);

DELETE FROM posts WHERE user_id NOT IN (SELECT id FROM app_users);
SELECT * FROM posts;

Result:

DELETE FROM posts WHERE user_id NOT IN (SELECT id FROM app_users)

1 row(s) deleted

SELECT * FROM posts

(1 row)

EXISTS, NOT EXISTS

Syntax:

EXISTS (SELECT ... FROM ... [WHERE ...])
NOT EXISTS (SELECT ... FROM ... [WHERE ...])

EXISTS is true when the subquery returns at least one row. What the subquery selects does not matter (SELECT 1 is usual).

• Returns: TRUE or FALSE, never NULL.
• NULL: a row full of NULL values still counts as a row.
• Notes: because the subquery cannot use outer columns, EXISTS is the same for every row: all rows are kept or none. It is useful as an on/off switch ("only if there is any big trade today"). To test each row ("securities that have trades"), use IN (SELECT ...) or a join.

Example: Show today's candles only if today's trade log is loaded (sample data: sample)

SELECT symbol, close
FROM ochl_day
WHERE time IN '2026-09-10'
  AND EXISTS (SELECT 1 FROM trade_log WHERE date IN '2026-09-10');

Result:

(3 rows)

Example: As a column: is there any trade of 1000 shares or more? (sample data: sample)

SELECT EXISTS (SELECT 1 FROM trade_log WHERE quantity >= 1000) AS big_trade,
       NOT EXISTS (SELECT 1 FROM trade_log WHERE quantity > 5000) AS no_huge_trade;

Result:

(1 row)

Example: Accounting check: warn when a journal entry does not balance

Setup:

CREATE TABLE journal (entry_id LONG, line INT, account STRING, debit DECIMAL(2), credit DECIMAL(2));
INSERT INTO journal VALUES
  (1, 1, 'Cash', 5000.00, 0), (1, 2, 'Sales', 0, 5000.00),
  (2, 1, 'Rent', 1200.00, 0), (2, 2, 'Cash', 0, 1000.00);

SELECT EXISTS (SELECT entry_id FROM journal GROUP BY entry_id HAVING sum(debit) != sum(credit)) AS has_unbalanced_entry;
SELECT entry_id, sum(debit) AS debit, sum(credit) AS credit
FROM journal GROUP BY entry_id HAVING sum(debit) != sum(credit);

Result:

SELECT EXISTS (SELECT entry_id FROM journal GROUP BY entry_id HAVING sum(debit) != sum(credit)) AS h

(1 row)

SELECT entry_id, sum(debit) AS debit, sum(credit) AS credit FROM journal GROUP BY entry_id HAVING su

(1 row)

FROM (SELECT ...) alias

Syntax:

SELECT ... FROM (SELECT ...) [AS] alias [JOIN ...] [WHERE ...] ...
SELECT ... FROM table t JOIN (SELECT ...) [AS] alias ON condition

Uses the result of a query as a table. This is also called a derived table. The subquery needs a name (the alias).

• Returns: the columns of the subquery, with their names or aliases. Give every computed column an alias, so the outer query can name it.
• Notes:
  • Use it to work in steps: make candles inside, compute an indicator outside; group inside, filter or join outside.
  • When the subquery has a TIMESTAMP column, the outer query can use SAMPLE BY on it.
  • A subquery in FROM or JOIN keeps all its rows in memory. Filter by time inside it for big tables.
  • One exception: when the outer query is a GROUP BY and the subquery is its only source, and the subquery (or each UNION ALL part of it) is a plain SELECT of one table with no GROUP BY, DISTINCT, ORDER BY or LIMIT of its own, its rows go straight into the groups, one after another. Only the groups are kept, so millions of rows fit (and very many groups go to disk, see big GROUP BY).

Example: Group inside, filter outside (sample data: sample)

SELECT t.symbol, t.trades, t.shares
FROM (SELECT symbol, count(*) AS trades, sum(quantity) AS shares FROM trade_log GROUP BY symbol) t
WHERE t.trades > 5
ORDER BY t.shares DESC;

Result:

(3 rows)

Example: Weekly candles inside, 2-week moving average outside (sample data: sample)

SELECT time, close, sma(close, 2) AS sma2
FROM (
  SELECT time, last(close) AS close
  FROM ochl_day
  WHERE symbol = 'CITY' AND time IN '2026'
  SAMPLE BY 1w WEEK START SUNDAY
) w;

Result:

(3 rows)

Example: Join each trade to the symbol's total for the day (sample data: sample)

SELECT f.transaction_id, f.symbol, f.quantity, d.total,
       round(f.quantity * 100.0 / d.total, 1) AS pct_of_day
FROM trade_log f
JOIN (SELECT symbol, sum(quantity) AS total FROM trade_log WHERE date IN '2026-09-10' GROUP BY symbol) AS d
  ON d.symbol = f.symbol
WHERE f.date IN '2026-09-10' AND f.symbol = 'COAST'
ORDER BY pct_of_day DESC
LIMIT 4;

Result:

(4 rows)

Example: A cash book from two tables, with a running balance

Setup:

CREATE TABLE sales (id LONG PRIMARY KEY, day DATE, amount DECIMAL(2));
CREATE TABLE expenses (id LONG PRIMARY KEY, day DATE, amount DECIMAL(2));
INSERT INTO sales VALUES (1, '2026-09-01', 25000.00), (2, '2026-09-03', 18000.00);
INSERT INTO expenses VALUES (1, '2026-09-02', 12000.00), (2, '2026-09-03', 2500.50);

SELECT day, kind, amount, cumsum(amount) OVER (ORDER BY day, kind) AS balance
FROM (
  SELECT day, 'sale' AS kind, amount FROM sales
  UNION ALL
  SELECT day, 'expense', -amount FROM expenses
) book
ORDER BY day, kind;

Result:

(4 rows)

Example: The subquery needs no name (sample data: sample)

SELECT count(*) FROM (SELECT symbol FROM securities);

Result:

(1 row)

Correlated subqueries

Syntax:

SELECT ..., (SELECT ... FROM t2 WHERE t2.col = t1.col) FROM t1
SELECT ... FROM t1 WHERE [NOT] EXISTS (SELECT 1 FROM t2 WHERE t2.col = t1.col)
SELECT ... FROM t1 LEFT JOIN LATERAL (SELECT ... WHERE t2.col = t1.col) x ON TRUE

A correlated subquery uses a column of the outer query (t1.col above).

• Speed: KDB does not run it again for every outer row. When the subquery joins the outer row with = (t2.col = t1.col), it runs once per different outer value first, with that value as a constant, so an index on t2.col answers it. Once the outer query has asked for more values than that pays for (one value per 256 rows of t2 when col has an index or is the primary key, at least 8; 8 without an index), KDB reads t2 once, builds a hash table by col and looks every further outer row up in it. So a page about one user costs a few index lookups (0.4 ms where the old plan took 930 ms on 300,000 logins), and a report over every user costs one pass over t2. Other subqueries run once per different outer value.
• Returns: a value subquery gives NULL when no row matches (count(*) gives 0). It is an error when it finds more than one row; add LIMIT 1 or an aggregate.
• Notes: a join with GROUP BY or a window function (lag(x) OVER (PARTITION BY ...)) gives the same answers and is often easier to read.

Example: The best close of each symbol, as a subquery per row (sample data: sample)

SELECT s.symbol,
       (SELECT max(close) FROM ochl_day d WHERE d.symbol = s.symbol) AS best_close
FROM securities s
ORDER BY s.symbol;

Result:

(5 rows)

Example: The same question as a LEFT JOIN with GROUP BY (sample data: sample)

SELECT s.symbol, max(d.close) AS best_close
FROM securities s
LEFT JOIN ochl_day d ON d.symbol = s.symbol
GROUP BY s.symbol
ORDER BY s.symbol;

Result:

(5 rows)

Example: Symbols with at least one trade: EXISTS with an outer column (sample data: sample)

SELECT symbol FROM securities s
WHERE EXISTS (SELECT 1 FROM trade_log f WHERE f.symbol = s.symbol)
ORDER BY symbol;

Result:

(4 rows)

Example: The same test with IN (SELECT ...) (sample data: sample)

SELECT symbol FROM securities
WHERE symbol IN (SELECT symbol FROM trade_log)
ORDER BY symbol;

Result:

(4 rows)

Example: Latest close per symbol compared with the day before: a window function instead (sample data: sample)

SELECT symbol, prev_close, close, round((close - prev_close) / prev_close * 100, 2) AS change_pct
FROM (
  SELECT time, symbol, close, lag(close) OVER (PARTITION BY symbol ORDER BY time) AS prev_close
  FROM ochl_day
) t
WHERE time IN '2026-09-10'
ORDER BY change_pct DESC;

Result:

(3 rows)