Joins

Combine rows from multiple tables using INNER, LEFT, FULL OUTER, and CROSS joins.

INNER JOIN

SELECT left_t.id, lval, rval
FROM left_t INNER JOIN right_t ON left_t.id = right_t.id;
-- Returns only matching rows
-- (2, 200, 20), (3, 300, 30)

LEFT JOIN

SELECT left_t.id, lval, rval
FROM left_t LEFT JOIN right_t ON left_t.id = right_t.id;
-- Returns all left rows, NULLs for non-matching right
-- 4 rows total

CROSS JOIN

SELECT COUNT(*) FROM left_t CROSS JOIN right_t;
-- 4 * 3 = 12 rows

Join with WHERE

SELECT left_t.id, lval, rval
FROM left_t INNER JOIN right_t ON left_t.id = right_t.id
WHERE left_t.id > 2;
-- (3, 300, 30)

Join with Aggregation

SELECT left_t.id, SUM(rval)
FROM left_t INNER JOIN right_t ON left_t.id = right_t.id
GROUP BY left_t.id;
-- (2, 20), (3, 30)

Multi-Table Joins

SELECT left_t.id, lval, rval, t3val
FROM left_t
INNER JOIN right_t ON left_t.id = right_t.id
INNER JOIN t3 ON left_t.id = t3.id;
-- (2, 200, 20, 2000), (3, 300, 30, 3000)

Join with String Columns

SELECT str_l.id, name, city
FROM str_l INNER JOIN str_r ON str_l.id = str_r.id;
-- (1, 'alice', 'london'), (2, 'bob', 'paris')