Malloy Documentation

join_types

Allow you to use INNER, RIGHT and FULL joins.

We'll likely allow this syntax in Queries, but probably not in sources (it can create confusion). At the moment this syntax works everywhere.

join_<one|many>: foo [<left|inner|right|full>] on ...

##! experimental {join_types}

Example Usage.

Two sources, flights an carriers.

source: flights is duckdb.table('../data/flights.parquet') extend {
  measure: flight_count is count()
}

source: carriers is duckdb.table('../data/carriers.parquet') -> {
  where: nickname = 'Southwest' | 'Delta'
  select: *
} extend {
  measure: carrier_count is count()
}

run: carriers -> {select: *}

QUERY RESULTS

code	name	nickname
WN	Southwest Airlines	Southwest
DL	Delta Air Lines	Delta

[
  {
    "code": "WN",
    "name": "Southwest Airlines",
    "nickname": "Southwest"
  },
  {
    "code": "DL",
    "name": "Delta Air Lines",
    "nickname": "Delta"
  }
]

WITH __stage0 AS (
  SELECT 
     base."code" as "code",
     base."name" as "name",
     base."nickname" as "nickname"
  FROM '../data/carriers.parquet' as base
  WHERE (base."nickname"='Southwest')or(base."nickname"='Delta')
)
SELECT 
   carriers."code" as "code",
   carriers."name" as "name",
   carriers."nickname" as "nickname"
FROM __stage0 as carriers

run: flights -> flight_count

QUERY RESULTS

flight_count
344,827

[
  {
    "flight_count": 344827
  }
]

SELECT 
   COUNT( 1) as "flight_count"
FROM '../data/flights.parquet' as flights

Using an inner join in a query.

Limits flights just Southwest and Delta flights.

run: carriers -> {
  extend: {
    join_many: flights inner on code = flights.carrier
  }
  aggregate: flights.flight_count
}

QUERY RESULTS

flight_count
120,881

[
  {
    "flight_count": 120881
  }
]

WITH __stage0 AS (
  SELECT 
     base."code" as "code",
     base."name" as "name",
     base."nickname" as "nickname"
  FROM '../data/carriers.parquet' as base
  WHERE (base."nickname"='Southwest')or(base."nickname"='Delta')
)
SELECT 
   COUNT(DISTINCT flights_0."__distinct_key") as "flight_count"
FROM __stage0 as carriers
 INNER JOIN (SELECT GEN_RANDOM_UUID() as __distinct_key, x.*  FROM '../data/flights.parquet' as x) AS flights_0
  ON carriers."code"=flights_0."carrier"