How to Use Subqueries in ParadeDB on PostgreSQL

How do I use subqueries efficiently in ParadeDB?

A subquery is a SELECT statement nested inside another SQL command, allowing multi-step filtering, aggregation, and data transformation in ParadeDB.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What problems do subqueries solve in ParadeDB?

Subqueries let you break complex logic into smaller steps: filter parent queries, build on intermediate aggregates, or feed derived tables to JOINs without creating temporary tables.

Which clause can hold a subquery?

ParadeDB (built on PostgreSQL) accepts subqueries in SELECT, FROM, WHERE, HAVING, INSERT, UPDATE, and DELETE clauses, giving you flexible, composable SQL.

How do I write a scalar subquery in the SELECT list?

Wrap a SELECT that returns one row and one column in parentheses.It acts like a single value in the outer query.

SELECT id, (SELECT COUNT(*) FROM OrderItems oi WHERE oi.order_id = o.id) AS item_count FROM Orders o;

When should I use subqueries in the FROM clause?

Use FROM-level subqueries—also called derived tables—when you need to JOIN or re-filter an intermediate result set.Give it an alias right after the closing parenthesis.

SELECT c.name, recent_orders.total FROM ( SELECT customer_id, SUM(total_amount) AS total FROM Orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days' GROUP BY customer_id ) recent_orders JOIN Customers c ON c.id = recent_orders.customer_id;

How do correlated subqueries work?

A correlated subquery references columns from the outer query, executing once per outer row.They excel at row-wise checks like existence tests.

SELECT name FROM Products p WHERE EXISTS ( SELECT 1 FROM OrderItems oi WHERE oi.product_id = p.id);

How can I improve subquery performance?

Add indexes on join and filter columns, prefer EXISTS/NOT EXISTS over IN/NOT IN for large sets, and move heavy work to common table expressions (CTEs) when readability matters.

Best practice: use CTEs vs. subqueries?

CTEs improve readability and can be referenced multiple times, but they materialize in older PostgreSQL versions.For one-off, simple nesting, subqueries are faster.

Why How to Use Subqueries in ParadeDB on PostgreSQL is important

How to Use Subqueries in ParadeDB on PostgreSQL Example Usage


-- List customers who spent above the average order total in the last 90 days
SELECT c.id, c.name, spend.total_spent
FROM (
      SELECT customer_id, SUM(total_amount) AS total_spent
      FROM Orders
      WHERE order_date > CURRENT_DATE - INTERVAL '90 days'
      GROUP BY customer_id
     ) spend
JOIN Customers c ON c.id = spend.customer_id
WHERE spend.total_spent > (
      SELECT AVG(total_amount)
      FROM Orders
      WHERE order_date > CURRENT_DATE - INTERVAL '90 days');

How to Use Subqueries in ParadeDB on PostgreSQL Syntax


-- Scalar subquery in SELECT
SELECT id,
       (SELECT COUNT(*) FROM OrderItems oi WHERE oi.order_id = o.id) AS item_count
FROM Orders o;

-- Derived table in FROM
SELECT c.name, recent.total
FROM (
      SELECT customer_id, SUM(total_amount) AS total
      FROM Orders
      GROUP BY customer_id
     ) recent
JOIN Customers c ON c.id = recent.customer_id;

-- Correlated subquery with EXISTS
SELECT name
FROM Products p
WHERE EXISTS (SELECT 1 FROM OrderItems oi WHERE oi.product_id = p.id);

Common Mistakes

Returning multiple rows in a scalar context. If your subquery yields more than one row, PostgreSQL throws “more than one row returned.” Fix by adding LIMIT 1, extra filters, or by using IN instead of =.
Using IN for large lists. IN forces materialization and may scan full sets. Replacing IN with EXISTS or JOINs usually performs better, especially with indexed columns.

Frequently Asked Questions (FAQs)

Can I nest subqueries multiple levels deep?

Yes, ParadeDB follows PostgreSQL rules and supports arbitrarily deep nesting, but readability and performance can degrade. Limit nesting to two levels and refactor with CTEs when queries grow complex.

Do subqueries in SELECT run for every row?

Yes, unless they are uncorrelated and can be folded as constants. Use JOINs or move heavy logic to CTEs if you see performance issues.

Are CTEs faster than subqueries?

Not always. Until PostgreSQL 12, CTEs were optimization fences. Modern versions inline simple CTEs, aligning their speed with subqueries. Benchmark both approaches for critical queries.