SQL Queries

SELECT and Joins

PostgreSQL supports INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins. FULL OUTER is useful for reconciliation reports finding rows in either table without match.

Use explicit JOIN syntax rather than comma-FROM with WHERE conditions—clearer and safer against accidental Cartesian products.

SELECT o.id, o.total, u.email
FROM orders o
INNER JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;

Aggregations

GROUP BY with HAVING filters grouped results. PostgreSQL supports FILTER clause for conditional aggregates—cleaner than CASE inside COUNT/SUM.

Distinct aggregates and ordered-set aggregates (percentile_cont) extend analytics capabilities beyond basic SQL.

SELECT
  user_id,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
  SUM(total) AS revenue
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000;

Subqueries

Scalar, row, and table subqueries nest in SELECT, FROM, and WHERE. LATERAL joins correlate subqueries per outer row—powerful for top-N per group patterns.

EXISTS often outperforms IN for large subquery results. Materialized subqueries in FROM should be named aliases.

SELECT u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 500
);

Common Table Expressions

WITH clauses define readable multi-step queries. Recursive CTEs traverse hierarchies—org charts, bill of materials, graph paths.

CTEs in PostgreSQL 12+ can be inlined or materialized with NOT MATERIALIZED / MATERIALIZED hints when you understand planner behavior.

• Recursive CTEs need UNION ALL and termination condition
• Use CTEs for readability; verify EXPLAIN for performance on huge datasets
• Multiple CTEs chain in one WITH clause comma-separated

WITH regional_sales AS (
  SELECT region, SUM(amount) AS total FROM sales GROUP BY region
)
SELECT region, total
FROM regional_sales
WHERE total > (SELECT AVG(total) FROM regional_sales);

Pagination and Performance

Prefer keyset pagination on indexed columns over OFFSET for large tables. Use cursor-based APIs returning last seen id.

Analyze queries with EXPLAIN (ANALYZE, BUFFERS) for actual timings and I/O. Create indexes matching WHERE and ORDER BY together.

SELECT * FROM products
WHERE id > $1
ORDER BY id
LIMIT 50;