Working with JOINs in ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/starter_guides/working_with_joins.md)

ClickHouse fully supports standard SQL joins, enabling efficient data analysis.
In this guide, you'll explore some of the available commonly used join types and how to use them with the help of Venn diagrams and example queries on a normalized [IMDB](https://en.wikipedia.org/wiki/IMDb) dataset originating from the [relational dataset repository](https://relational.fit.cvut.cz/dataset/IMDb).

## Test Data and Resources[​](#test-data-and-resources "Direct link to Test Data and Resources")

Instructions for creating and loading the tables can be found [here](/docs/integrations/dbt/guides).
The dataset is also available in the [playground](https://sql.clickhouse.com?query_id=AACTS8ZBT3G7SSGN8ZJBJY) if you don't want to create and load
the tables locally.

You'll use the following four tables from the example dataset:

The data in these four tables represent movies which can have one or many genres.
The roles in a movie are played by actors.

The arrows in the diagram above represent [foreign-to-primary-key-relationships](https://en.wikipedia.org/wiki/Foreign_key). e.g. the `movie_id` column of a row in the `genres` table contains the `id` value from a row in the `movies` table.

There is a [many-to-many relationship](https://en.wikipedia.org/wiki/Many-to-many_(data_model)) between movies and actors.
This many-to-many relationship is normalized into two [one-to-many relationships](https://en.wikipedia.org/wiki/One-to-many_(data_model)) by using the `roles` table.
Each row in the `roles` table contains the values of the `id` columns of the `movies` table and the `actors` table.

## Join types supported in ClickHouse[​](#join-types-supported-in-clickhouse "Direct link to Join types supported in ClickHouse")

ClickHouse supports the following join types:

- [INNER JOIN](#inner-join)
- [OUTER JOIN](#left--right--full-outer-join)
- [CROSS JOIN](#cross-join)
- [SEMI JOIN](#left--right-semi-join)
- [ANTI JOIN](#left--right-anti-join)
- [ANY JOIN](#left--right--inner-any-join)
- [ASOF JOIN](#asof-join)

You'll write example queries for each of the JOIN types above in the following sections.

## INNER JOIN[​](#inner-join "Direct link to INNER JOIN")

The `INNER JOIN` returns, for each pair of rows matching on join keys, the column values of the row from the left table, combined with the column values of the row from the right table.
If a row has more than one match, then all matches are returned (meaning that the [cartesian product](https://en.wikipedia.org/wiki/Cartesian_product) is produced for rows with matching join keys).

This query finds the genres for each movie by joining the `movies` table with the `genres` table:

```
SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
INNER JOIN genres AS g ON m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;
```

```
┌─name───────────────────────────────────┬─genre─────┐
│ Harry Potter and the Half-Blood Prince │ Action    │
│ Harry Potter and the Half-Blood Prince │ Adventure │
│ Harry Potter and the Half-Blood Prince │ Family    │
│ Harry Potter and the Half-Blood Prince │ Fantasy   │
│ Harry Potter and the Half-Blood Prince │ Thriller  │
│ DragonBall Z                           │ Action    │
│ DragonBall Z                           │ Adventure │
│ DragonBall Z                           │ Comedy    │
│ DragonBall Z                           │ Fantasy   │
│ DragonBall Z                           │ Sci-Fi    │
└────────────────────────────────────────┴───────────┘
```

Note

The `INNER` keyword can be omitted.

The behavior of the `INNER JOIN` can be extended or changed, by using one of the following other join types.

## (LEFT / RIGHT / FULL) OUTER JOIN[​](#left--right--full-outer-join "Direct link to (LEFT / RIGHT / FULL) OUTER JOIN")

The `LEFT OUTER JOIN` behaves like `INNER JOIN`; plus, for non-matching left table rows, ClickHouse returns [default values](/docs/sql-reference/statements/create/table#default_values) for the right table’s columns.

A `RIGHT OUTER JOIN` query is similar and also returns values from non-matching rows from the right table together with default values for the columns of the left table.

A `FULL OUTER JOIN` query combines the `LEFT` and `RIGHT OUTER JOIN` and returns values from non-matching rows from the left and the right table, together with default values for the columns of the right and left table, respectively.

Note

ClickHouse can be [configured](/docs/operations/settings/settings#join_use_nulls) to return [NULL](/docs/sql-reference/syntax#null)s instead of default values (however, for [performance reasons](/docs/sql-reference/data-types/nullable#storage-features), that is less recommended).

This query finds all movies that have no genre by querying for all rows from the `movies` table that don’t have matches in the `genres` table, and therefore get (at query time) the default value 0 for the `movie_id` column:

```
SELECT m.name
FROM movies AS m
LEFT JOIN genres AS g ON m.id = g.movie_id
WHERE g.movie_id = 0
ORDER BY
    m.year DESC,
    m.name ASC
LIMIT 10;
```

```
┌─name──────────────────────────────────────┐
│ """Pacific War, The"""                    │
│ """Turin 2006: XX Olympic Winter Games""" │
│ Arthur, the Movie                         │
│ Bridge to Terabithia                      │
│ Mars in Aries                             │
│ Master of Space and Time                  │
│ Ninth Life of Louis Drax, The             │
│ Paradox                                   │
│ Ratatouille                               │
│ """American Dad"""                        │
└───────────────────────────────────────────┘
```

Note

The `OUTER` keyword can be omitted.

## CROSS JOIN[​](#cross-join "Direct link to CROSS JOIN")

The `CROSS JOIN` produces the full cartesian product of the two tables without considering join keys.
Each row from the left table is combined with each row from the right table.

The following query, therefore, is combing each row from the `movies` table with each row from the `genres` table:

```
SELECT
    m.name,
    m.id,
    g.movie_id,
    g.genre
FROM movies AS m
CROSS JOIN genres AS g
LIMIT 10;
```

```
┌─name─┬─id─┬─movie_id─┬─genre───────┐
│ #28  │  0 │        1 │ Documentary │
│ #28  │  0 │        1 │ Short       │
│ #28  │  0 │        2 │ Comedy      │
│ #28  │  0 │        2 │ Crime       │
│ #28  │  0 │        5 │ Western     │
│ #28  │  0 │        6 │ Comedy      │
│ #28  │  0 │        6 │ Family      │
│ #28  │  0 │        8 │ Animation   │
│ #28  │  0 │        8 │ Comedy      │
│ #28  │  0 │        8 │ Short       │
└──────┴────┴──────────┴─────────────┘
```

While the previous example query alone didn’t make much sense, it can be extended with a `WHERE` clause for associating matching rows to replicate `INNER JOIN` behavior for finding the genres for each movie:

```
SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
CROSS JOIN genres AS g
WHERE m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;
```

An alternative syntax for `CROSS JOIN` specifies multiple tables in the `FROM` clause separated by commas.

ClickHouse is [rewriting](https://github.com/ClickHouse/ClickHouse/blob/23.2/src/Core/Settings.h#L896) a `CROSS JOIN` to an `INNER JOIN` if there are joining expressions in the `WHERE` section of the query.

You can check that for the example query via [EXPLAIN SYNTAX](/docs/sql-reference/statements/explain#explain-syntax) (that returns the syntactically optimized version into which a query gets rewritten before being [executed](https://youtu.be/hP6G2Nlz_cA)):

```
EXPLAIN SYNTAX
SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
CROSS JOIN genres AS g
WHERE m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;
```