JOINs Between Tables

A query often needs to retrieve data from more than one table at the same time. In DuckDB, this is done with the JOIN statement.

Preparing Sample Data

Assume we have two tables:

The students table:

The grades table:

INNER JOIN

INNER JOIN only returns rows that have a match in both tables:

SELECT m.name, n.course, n.score
FROM students m
INNER JOIN grades n ON m.id = n.student_id;

The result:

Note: Students without grades will not appear in the INNER JOIN result.

LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN returns all rows from the left table (students), even if there is no match in the right table (grades). Columns from the right table are filled with NULL when there is no match:

SELECT m.name, n.course, n.score
FROM students m
LEFT JOIN grades n ON m.id = n.student_id;

The result:

If a student has no grades, their row still appears with score set to NULL.

Using Table Aliases

In the example above, we used the alias m for students and n for grades. Aliases make queries more concise and easier to read, especially when table names are long.

Joining More Than Two Tables

You can combine multiple JOINs in a single query:

SELECT m.name, n.course, n.score, j.major_name
FROM students m
INNER JOIN grades n ON m.id = n.student_id
INNER JOIN major j ON m.major = j.major_code;

Summary

In this chapter, we learned how to combine data from multiple tables with INNER JOIN and LEFT JOIN, and how to use table aliases to write more concise queries.

In the next chapter, we'll learn about aggregate functions such as SUM, AVG, COUNT, MAX, and MIN, together with the GROUP BY clause.