JOINs and set operations

TLDR: you can use all kind of JOINS and set operators

Joining Tables

Use standard JOIN syntax:

SELECT  COUNT(*)
FROM    Aero.DIM_DESTINATIONS AS dd
        JOIN Aero.FACT_DEPARTURES AS fd ON dd.DESTINATION_ID = fd.DESTINATION_ID
WHERE   dd.DESTINATION_CITY = 'Zurich'

You can even use a simpler syntax for JOIN if the column names are equal and the values are required to be equal:

SELECT  COUNT(*)
FROM    Aero.DIM_DESTINATIONS AS dd
        JOIN Aero.FACT_DEPARTURES AS fd USING(DESTINATION_ID)
WHERE   dd.DESTINATION_CITY = 'Zurich'

Now a little test: what kinds of JOINs you know?

…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…
…

All are supprted: INNER, LEFT, RIGHT, FULL, CROSS.

But: have you heard of conditional JOINs, SEMI and ANTI JOINS, positional JOINs, lateral JOINs, As-Of JOINs? Well, you might not need them when testing MS Excel data probably, but all are supported.

See details here: https://duckdb.org/docs/sql/query_syntax/from.

Set Operations

All common operations are supported: UNION, UNION ALL, INTERSECT, EXCEPT.

You can also use UNION [ALL] BY NAME to union the sets based on column name, instead of position.

See details here: https://duckdb.org/docs/sql/query_syntax/setops.

← SELECT syntax
Expressions →