A JOIN combines rows from multiple relations when their keys or conditions match. It extends the FROM clause so one query can analyze related data together. section 3.4.4 section 6.2.2
Match rows on a shared key
SELECT * FROM (VALUES (1, 'a1'), (2, 'a2'), (3, 'a3')) l(id, nameA) JOIN (VALUES (1, 'b1'), (2, 'b2'), (4, 'b4')) r(id, nameB) USING (id); The inner join returns only IDs 1 and 2, because those keys occur in both relations. A LEFT OUTER JOIN keeps the left-side ID 3 with an empty right-side value; a FULL OUTER JOIN also keeps the right-only ID 4. section 3.4.4
Choose the join form according to which unmatched rows must remain:
- JOIN or INNER JOIN returns only matching pairs. section 3.4.4
- LEFT OUTER JOIN preserves every row from the left relation and fills missing right-side columns. section 3.4.4
- FULL OUTER JOIN preserves unmatched rows from both relations. section 3.4.4
- USING (id) joins on a shared key and avoids repeating the duplicate key column in the result. section 3.4.4
- Duplicate keys multiply matching rows, so repeated keys can expand the result. section 3.4.4
Joins also connect staged queries and file-based workflows: a subquery or CTE can be joined back to readings, while DuckDB can join CSV files on timestamps before writing a combined result. section 3.4.4
Listing 3.12 Using an inner join
SELECT *
FROM
(VALUES (1, 'a1'),
(2, 'a2'),
(3, 'a3')) l(id, nameA)
JOIN
(VALUES (1, 'b1'),
(2, 'b2'),
(4, 'b4')) r(id, nameB)
USING (id); #1
Runnable inner-join example showing VALUES relations, shared-key matching, and USING syntax. section 3.4.4
