Joins

INNER, LEFT, RIGHT, FULL, CROSS, USING, lateral, table functions, and execution shapes.

Version
Latest
v0.1.0 · latest 1 min read
On this page
  1. Execution
  2. What helps performance
sqlsource
SELECT * FROM a JOIN b ON a.id = b.id;
SELECT * FROM a LEFT JOIN b ON a.id = b.id;
SELECT * FROM a RIGHT JOIN b USING (id);
SELECT * FROM a FULL JOIN b ON a.id = b.id;
SELECT * FROM a CROSS JOIN b;   SELECT * FROM a, b;
SELECT * FROM users u, LATERAL jsonb_each(u.payload) AS e;
SELECT * FROM generate_series(1,3) AS g(n) WITH ORDINALITY;

Inner, left, right, full, and cross joins are supported. USING is expanded to equalities. FROM t x(a,b) derived column aliases are supported.

Execution#

Nested-loop / hash / indexed / lateral shapes; SELECT * and t.* expansion in join order; ambiguous column → error; implicit lateral when RHS references outer columns.

What helps performance#

Equality and range join keys benefit from B-tree indexes; time-ordered joins benefit from ts indexes + zone-map pruning on columnar segments. Verify with EXPLAIN.

Was this page helpful?