DOCS
← Back to Glossary
JOIN clause
How do SQL JOINs work? Learn INNER, LEFT, RIGHT, FULL OUTER, CROSS, and ASOF joins with syntax examples — plus how to join directly against CSV and Parquet files.
Overview
A JOIN clause combines rows from two or more tables based on a related column (often a foreign key) between them. It's one of the most fundamental operations in SQL, allowing you to bring together data that has been normalized across different tables. Think of it like connecting puzzle pieces - each table holds part of the information, and the join brings those pieces together into a complete picture.
NOTE
This is an overview. If you want to understand the details of how joins work in DuckDB, check out Chapter 3 in the free DuckDB in Action book.
Basic Syntax
The most common join syntax in DuckDB follows this pattern:
Copy code
SELECT * FROM table1
JOIN table2 ON table1.column = table2.column
Join Types in DuckDB SQL
Inner Join (default)
An inner join combines rows from two or more tables based on a matching condition, keeping only the rows where there is a match in both tables. Think of it like a Venn diagram - it returns just the overlapping part.
Copy code
SELECT orders.id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id
Left Outer Join
A left outer join returns all records from the left table (the first one mentioned) and only matching records from the right table. If there's no match in the right table, the result will contain NULL values for those columns.
The Left Outer Join (LEFT JOIN) is one of the most common types of joins in data analytics.
Copy code
SELECT products.name, reviews.rating
FROM products
LEFT JOIN reviews ON products.id = reviews.product_id
Right Outer Join
A right outer join is essentially the mirror image of a left outer join - it returns all records from the right table (the second one mentioned) and only matching records from the left table. If there's no match in the left table, NULL values are used for those columns.
Copy code
SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.id
Full Outer Join
A full outer join (also called a full join) combines both left and right outer joins - it returns ALL records from BOTH tables. Where there are matches, it joins the data; where there's no match on either side, it fills in NULL values for the missing data.
Copy code
SELECT students.name, classes.name
FROM students
FULL OUTER JOIN classes ON students.class_id = classes.id
Cross Join (creates Cartesian product)
A cross join (also called a Cartesian join) creates a result set that combines every row from the first table with every row from the second table. It creates all possible combinations without any matching condition.
Copy code
SELECT employees.name, shifts.time
FROM employees
CROSS JOIN shifts
DuckDB-Specific Features
DuckDB includes special join types not commonly found in other databases:
ASOF Join
An ASOF (As-Of) join in DuckDB is a special type of join designed for time-series data that matches records based on the closest previous timestamp. It's particularly useful when working with data streams that have different sampling rates or when you need to align events with the most recent prior state.
Copy code
SELECT trades.price, quotes.bid
FROM trades
ASOF JOIN quotes ON trades.timestamp >= quotes.timestamp
Joining against files and subqueries
Additionally, DuckDB supports joining directly against files and subqueries without needing to create temporary tables first. For example:
Copy code
SELECT t1.*, t2.revenue
FROM 'sales.parquet' t1
JOIN (SELECT * FROM 'revenue.csv') t2 ON t1.id = t2.id
Performance Considerations
DuckDB automatically optimizes join operations using various strategies including hash joins and merge joins. Unlike some databases that require explicit join hints, DuckDB's query optimizer typically selects the most efficient join strategy based on the data characteristics and available indexes.
Related terms
CROSS JOIN →
A CROSS JOIN produces the Cartesian product of two tables, pairing every row from the first table with every row from the second.
NATURAL JOIN →A NATURAL JOIN automatically joins two tables on all columns that share the same name, without an explicit ON or USING clause.
ASOF JOIN →An ASOF JOIN matches each row in one table to the closest preceding (or following) row in another table based on an ordering column, commonly a timestamp -- a core building block for time-series analysis.
USING clause →The USING clause specifies join columns by name when both tables share identically named columns, joining on equality and returning a single copy of each shared column.
SELF JOIN →A self join joins a table to itself, typically using table aliases to compare rows within the same table to each other.
Query optimization →Query optimization is the process by which a database transforms a SQL query into an efficient execution plan, choosing among logically equivalent strategies for the one expected to run fastest.
FAQS
A JOIN combines rows from two or more tables based on a related column—often a foreign key—so you can bring together data that has been normalized across separate tables.
The common types are INNER JOIN (matching rows only), LEFT and RIGHT OUTER JOIN (all rows from one side plus matches), FULL OUTER JOIN (all rows from both), and CROSS JOIN (every combination). DuckDB supports all standard join types.
Use SELECT ... FROM a JOIN b ON a.key = b.key. Put the join type (e.g. LEFT JOIN) before the second table and the matching condition in the ON clause.
