Joins

immudb supports all standard SQL join types for combining rows from multiple tables.

INNER JOIN#

Returns rows that have matching values in both tables.

1SELECT c.name, o.amount
2FROM customers c
3INNER JOIN orders o ON c.id = o.customer_id;

LEFT JOIN#

Returns all rows from the left table, with NULLs for unmatched right rows.

1SELECT c.name, o.amount
2FROM customers c
3LEFT JOIN orders o ON c.id = o.customer_id;

RIGHT JOIN#

Returns all rows from the right table, with NULLs for unmatched left rows.

1SELECT c.name, o.amount
2FROM customers c
3RIGHT JOIN orders o ON c.id = o.customer_id;

FULL OUTER JOIN#

Returns all rows from both tables, with NULLs where there is no match.

1SELECT c.name, o.amount
2FROM customers c
3FULL OUTER JOIN orders o ON c.id = o.customer_id;

CROSS JOIN#

Returns the Cartesian product (every combination of rows).

1SELECT c.name, p.name
2FROM colors c
3CROSS JOIN sizes p;

NATURAL JOIN#

Automatically joins on columns with matching names.

1SELECT * FROM orders NATURAL JOIN customers;

JOIN … USING#

Join on a specific shared column name.

1SELECT * FROM orders JOIN customers USING (customer_id);

LATERAL JOIN#

Correlated subqueries in the FROM clause. The subquery can reference columns from preceding tables.

1SELECT e.name, t.order_count
2FROM employees e,
3    LATERAL (
4        SELECT COUNT(*) AS order_count
5        FROM orders o
6        WHERE o.employee_id = e.id
7    ) t;