SELECT

Basic SELECT

SELECT name, age FROM users WHERE age > 18 ORDER BY age DESC LIMIT 10 OFFSET 20;
SELECT DISTINCT department FROM employees;

Aggregation

SELECT department, COUNT(*), AVG(salary), MAX(salary), MIN(salary), SUM(bonus)
  FROM employees
  GROUP BY department
  HAVING COUNT(*) > 5
  ORDER BY AVG(salary) DESC;

JOIN

INNER JOIN across N tables is supported; LEFT/RIGHT/FULL OUTER JOIN only for two tables.

SELECT u.name, p.title, c.body
  FROM users u
  INNER JOIN posts p ON u.id = p.author_id
  INNER JOIN comments c ON p.id = c.post_id;

Subqueries

SELECT * FROM users WHERE id IN (SELECT user_id FROM admins);

Correlated EXISTS/scalar subqueries are supported and get automatically decorrelated by the planner where possible — a meaningful speedup on larger result sets.

Window functions

Supported: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, plus aggregates (SUM/AVG/COUNT/MIN/MAX) used as window functions — with PARTITION BY, ORDER BY, and explicit ROWS BETWEEN ... AND ... frames; a window can sit over a plain SELECT, over a JOIN, or over a GROUP BY.

SELECT department, name, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees;

Not supported: RANGE with a numeric offset (needs peer-group semantics) — ROWS frames only.

CTEs (WITH ...)

Supported: one or more CTEs per query, joining a CTE with another CTE. Limitations: CTEs are non-recursive (no WITH RECURSIVE), a CTE body must be a plain SELECT, a CTE can be combined with at most one JOIN, and a CTE can't reference another CTE from the same WITH.

WITH top_customers AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, t.total
FROM top_customers t
JOIN customers c ON c.id = t.customer_id
ORDER BY t.total DESC
LIMIT 10;