Queries

SereneDB compiles queries into efficient code and executes them in parallel, enabling fast analytical scans over large datasets.

SELECT

SELECT title, year, rating
FROM movies
WHERE rating > 8.0
ORDER BY rating DESC
LIMIT 10;

Select specific columns to reduce I/O. Use * only when you need all columns.

WHERE

Filter rows with conditions:

SELECT title, views
FROM articles
WHERE category = 'engineering' AND views > 1000;

GROUP BY

Aggregate data with GROUP BY. Use HAVING to filter after aggregation:

SELECT category, COUNT(*) AS count, AVG(views) AS avg_views
FROM articles
GROUP BY category
HAVING COUNT(*) > 5;

JOINs

SereneDB supports all standard join types:

-- Inner join
SELECT a.title, s.name
FROM articles a
JOIN authors s ON a.author_id = s.id;

-- Left join
SELECT a.title, s.name
FROM articles a
LEFT JOIN authors s ON a.author_id = s.id;

Joins can be arbitrarily nested and combined.

Common Table Expressions (CTEs)

Use WITH to structure complex queries into named subqueries:

WITH top_articles AS (
    SELECT title, views, category
    FROM articles
    WHERE views > 5000
)
SELECT category, COUNT(*) AS count
FROM top_articles
GROUP BY category;

SereneDB automatically inlines CTEs and optimizes across all subqueries.

Window Functions

Compute values across a set of rows related to the current row:

SELECT title, category, views,
    RANK() OVER (PARTITION BY category ORDER BY views DESC) AS rank
FROM articles;

Include a tie-breaker in ORDER BY to get deterministic results.

Set Operations

Combine results from multiple queries:

-- Union (removes duplicates)
SELECT title FROM articles_2024
UNION
SELECT title FROM articles_2025;

-- Union all (keeps duplicates)
SELECT title FROM articles_2024
UNION ALL
SELECT title FROM articles_2025;

-- Intersect
SELECT title FROM articles_2024
INTERSECT
SELECT title FROM articles_2025;

Subqueries

Use subqueries in WHERE, FROM, or SELECT:

SELECT title, views
FROM articles
WHERE views > (SELECT AVG(views) FROM articles);

SELECT title, views
FROM articles
WHERE category IN (SELECT name FROM categories WHERE active = TRUE);