codesnips
sql 306 lines · 2 tabs

LATERAL joins and correlated subqueries

Maria Garcia Feb 2026
2 tabs 
-- Basic LATERAL join
SELECT
  u.username,
  recent.order_id,
  recent.total,
  recent.created_at
FROM users u
CROSS JOIN LATERAL (
  SELECT id AS order_id, total, created_at
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) AS recent;
-- Returns up to 3 most recent orders per user

-- LEFT JOIN LATERAL (include users with no orders)
SELECT
  u.username,
  recent.order_id,
  recent.total
FROM users u
LEFT JOIN LATERAL (
  SELECT id AS order_id, total
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) AS recent ON true;

-- Compare to traditional approach (slower)
SELECT
  u.username,
  o.id AS order_id,
  o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IN (
  SELECT id
  FROM orders o2
  WHERE o2.user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
);

-- Top N per group
SELECT
  c.name AS category,
  p.name AS product,
  p.price
FROM categories c
CROSS JOIN LATERAL (
  SELECT name, price
  FROM products
  WHERE category_id = c.id
  ORDER BY price DESC
  LIMIT 5
) p;

-- Multiple LATERAL joins
SELECT
  u.username,
  recent_order.total AS last_order_total,
  favorite_product.name AS favorite_product
FROM users u
LEFT JOIN LATERAL (
  SELECT total
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) recent_order ON true
LEFT JOIN LATERAL (
  SELECT p.name
  FROM orders o
  JOIN order_items oi ON o.id = oi.order_id
  JOIN products p ON oi.product_id = p.id
  WHERE o.user_id = u.id
  GROUP BY p.id, p.name
  ORDER BY COUNT(*) DESC
  LIMIT 1
) favorite_product ON true;

-- LATERAL with aggregates
SELECT
  u.username,
  stats.order_count,
  stats.total_spent,
  stats.avg_order
FROM users u
CROSS JOIN LATERAL (
  SELECT
    COUNT(*) AS order_count,
    SUM(total) AS total_spent,
    AVG(total) AS avg_order
  FROM orders
  WHERE user_id = u.id
) stats
WHERE stats.order_count > 0;

-- Set-returning function in LATERAL
SELECT
  u.username,
  day.date,
  day.order_count
FROM users u
CROSS JOIN LATERAL (
  SELECT
    DATE(created_at) AS date,
    COUNT(*) AS order_count
  FROM orders
  WHERE user_id = u.id
    AND created_at >= CURRENT_DATE - INTERVAL '7 days'
  GROUP BY DATE(created_at)
) day;

-- Generate series in LATERAL
SELECT
  p.name,
  date.day,
  COALESCE(sales.daily_sales, 0) AS sales
FROM products p
CROSS JOIN LATERAL
  generate_series(
    CURRENT_DATE - INTERVAL '30 days',
    CURRENT_DATE,
    INTERVAL '1 day'
  ) AS date(day)
LEFT JOIN LATERAL (
  SELECT SUM(oi.quantity * oi.price) AS daily_sales
  FROM order_items oi
  JOIN orders o ON oi.order_id = o.id
  WHERE oi.product_id = p.id
    AND DATE(o.created_at) = date.day
) sales ON true
WHERE p.id = 1
ORDER BY date.day;
2 files · sql Explain with highlit

LATERAL joins enable correlated subqueries in FROM clause. Each row can reference previous table columns. I use LATERAL for top-N-per-group queries. CROSS JOIN LATERAL iterates for each row. LEFT JOIN LATERAL includes rows without matches. Understanding LATERAL unlocks powerful query patterns. Function calls in LATERAL receive row values. Set-returning functions expand to multiple rows. LATERAL replaces complex subqueries with clearer syntax. Proper use improves both performance and readability. Essential for complex analytics, related records, rankings. LATERAL is PostgreSQL's secret weapon for advanced queries.

Related snips

sql 
-- Simple function
CREATE OR REPLACE FUNCTION get_full_name(
  first_name VARCHAR,
  last_name VARCHAR
)
RETURNS VARCHAR AS $$

Stored procedures and functions in PostgreSQL

postgresql stored-procedures functions
by Maria Garcia 2 tabs
sql 
-- EXPLAIN ANALYZE (actual execution statistics)
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'

Advanced query optimization techniques

database optimization query-performance
by Maria Garcia 2 tabs
sql 
-- Publisher: Send notification
NOTIFY new_order, 'Order #12345 created';

-- Subscriber: Listen for notifications
LISTEN new_order;

PostgreSQL LISTEN/NOTIFY for pub-sub messaging

postgresql listen-notify pub-sub
by Maria Garcia 2 tabs
sql 
-- Logical backup with pg_dump
-- Single database
-- pg_dump -h localhost -U postgres -d mydb -F c -f mydb_backup.dump

-- All databases
-- pg_dumpall -h localhost -U postgres -f all_databases.sql

Database backup and recovery strategies

database backup recovery
by Maria Garcia 2 tabs
sql 
-- Prepared statements basics
-- PostgreSQL syntax
PREPARE get_user (INT) AS
SELECT id, username, email
FROM users
WHERE id = $1;

Query plan caching and prepared statements

postgresql performance query-plans
by Maria Garcia 2 tabs
python 
from django.db.models import F, Window
from django.db.models.functions import RowNumber, Rank, DenseRank
from products.models import Sale


def get_sales_with_ranking():

Django ORM window functions for analytics

django python database
by Priya Sharma 1 tab

Share this code

Here's the card — post it anywhere.

LATERAL joins and correlated subqueries — share card
Link copied