intermediateCore SQLPostgreSQL Specific100% Free

SQL JOINs in PostgreSQL

Definition

PostgreSQL supports all standard SQL JOIN types plus LATERAL joins, which allow subqueries in the FROM clause to reference columns from preceding table references.

Introduction to SQL JOINs in PostgreSQL

PostgreSQL's JOIN support is comprehensive and includes unique features like LATERAL joins that make complex queries more elegant. Understanding PostgreSQL-specific join behavior and optimization tips is essential for building high-performance PostgreSQL applications.

Syntax

SQL

-- PostgreSQL LATERAL JOIN
SELECT e.name, recent_orders.*
FROM employees e
CROSS JOIN LATERAL (
  SELECT * FROM orders
  WHERE customer_id = e.id
  ORDER BY created_at DESC
  LIMIT 3
) AS recent_orders;

Examples

PostgreSQL LATERAL JOIN

SELECT c.name, top_products.product_name, top_products.revenue
FROM customers c
CROSS JOIN LATERAL (
  SELECT product_name, SUM(amount) AS revenue
  FROM orders
  WHERE customer_id = c.id
  GROUP BY product_name
  ORDER BY revenue DESC
  LIMIT 3
) AS top_products;

LATERAL joins allow the subquery to reference 'c.id' from the outer query. This elegantly returns the top 3 products per customer without window functions.

Try in Playground

PostgreSQL Full Text JOIN Pattern

SELECT e.name, d.name AS dept,
       to_tsvector(e.bio) @@ plainto_tsquery('engineer') AS is_engineer
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE to_tsvector(e.bio) @@ plainto_tsquery('engineer');

PostgreSQL full-text search operators can be combined with standard JOINs for powerful hybrid queries.

Try in Playground

Common Mistakes

Not using LATERAL when a subquery needs to reference outer columns

Using implicit CROSS JOIN (comma syntax) — explicit JOIN syntax is clearer

Forgetting to ANALYZE after large data changes — stale statistics lead to bad join plans

Not understanding hash join vs nested loop vs merge join strategies in EXPLAIN output

Frequently Asked Questions

What is a LATERAL JOIN in PostgreSQL?

A LATERAL join allows a subquery in the FROM clause to reference columns from table references that appear earlier in the FROM clause. Without LATERAL, subqueries cannot reference other tables in the same FROM list. LATERAL is essential for per-row top-N queries.

What join strategies does PostgreSQL use?

PostgreSQL's query planner chooses between three join strategies: Hash Join (builds hash table from one input, probes with other — good for large unsorted datasets), Nested Loop (iterates outer table, looks up inner — good with indexes), and Merge Join (sorts both inputs then scans — good when inputs are already sorted).

How do you improve JOIN performance in PostgreSQL?

Index foreign key columns, run ANALYZE to update statistics, use EXPLAIN ANALYZE to identify bottlenecks, consider partial indexes for frequently joined subsets, and use LATERAL instead of correlated subqueries for per-row top-N patterns.

Practice This in the SQL Playground

Write real queries, see live results, and master SQL JOINs in PostgreSQL hands-on. 100% free.

Open SQL Playground Browse All Lessons