Joins

VSCode

Joins in Malloy differ from SQL joins. When two sources are joined, Malloy retains the graph nature and hierarchy of the the data relationships. This is unlike SQL, which flattens everything into a single table space.

Aggregate calculations navigate this graph to deduce the locality of computation, so they are always computed correctly regardless of join pattern, avoiding the fan and chasm traps.

In Malloy, syntaxes for join are:

join_one: source name [is source] on boolean expression
join_one: source name [is source] with foreign key expression
join_many: source name [is source] on boolean expression
join_cross: source name [is source] [on boolean expression]

Examples of the above, with orders as the implied source:

join_one: users is duckdb.table('data/users.parquet') on user_id = users.id
join_one: users on user_id = users.id
join_one: users with user_id
join_many: order_items on order_items.id = id
join_cross: order_items2 is duckdb.table('data/order_items.parquet') on user_id = order_items2.user_id

join_one: - the table we are joining has one row for each row in the source table.

join_many: - the table we are joining has many rows for each row in the source table

join_cross: - the join is a cross product and there will be many rows in each side of the join.

Malloy's joins are left outer joins by default. Since Malloy deals in graphs, some SQL Join types don't make sense (RIGHT JOIN, for example).

Join Types

Foreign Key to Primary Key

The easiest, most error-proof way to perform a join is using the following syntax:

join_one: source with foreign key

To join based on a foreign key through the primary_key of a joined source, use with to specify an expression, which could be as simple as a field name in the source. This expression is matched against the declared primary_key of the joined source. Sources without a primary_key cannot use with joins.

source: users is duckdb.table('../data/users.parquet') extend {
  primary_key: id
}

source: order_items is duckdb.table('../data/order_items.parquet') extend {
  join_one: users with user_id
}

This is simply a shortcut, when joining based on the primary key of a joined source. It is exactly equivalent to the on join written like this.

source: order_items is duckdb.table('../data/order_items.parquet') extend {
  join_one: users on order_items.user_id = users.id
}

Naming Joined Sources

If no alias is specified using is, the name of the join will be the name of the source being joined.

source: carriers is duckdb.table('../data/carriers.parquet') extend {
  primary_key: code
  measure: carrier_count is count()
}

source: flights is duckdb.table('../data/flights.parquet') extend {
  join_one: carriers with carrier
}

To give the joined source a different name within the context source, use is to alias it.

source: airports is duckdb.table('../data/airports.parquet') extend {
  primary_key: code
}

source: flights2 is duckdb.table('../data/flights.parquet') extend {
  join_one: origin_airport is airports with origin
}

In-line Joins

Sources do not need to be modeled before they are used in a join, though the join must be named using is.

source: flights3 is duckdb.table('../data/flights.parquet') extend {
  join_one: carriers is 
    duckdb.table('../data/carriers.parquet') extend { primary_key: code } with carrier
}

Using Fields from Joined Sources

When a source is joined in, its fields become nested within the parent source. Fields from joined sources can be referenced using .:

run: flights3 -> {
  group_by: carriers.nickname
  aggregate: flight_count is count()
}

QUERY RESULTS

nickname	flight_count
Southwest	88,751
USAir	37,683
American	34,577
Northwest	33,580
United	32,757

[
  {
    "nickname": "Southwest",
    "flight_count": 88751
  },
  {
    "nickname": "USAir",
    "flight_count": 37683
  },
  {
    "nickname": "American",
    "flight_count": 34577
  },
  {
    "nickname": "Northwest",
    "flight_count": 33580
  },
  {
    "nickname": "United",
    "flight_count": 32757
  }
]

SELECT 
   carriers_0."nickname" as "nickname",
   COUNT( 1) as "flight_count"
FROM '../data/flights.parquet' as flights3
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON carriers_0."code"=flights3."carrier"
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Measures defined in joined sources may be used in addition to dimensions.

run: flights -> {
  group_by: destination
  aggregate: carriers.carrier_count
}

QUERY RESULTS

destination	carrier_count
DFW	13
ATL	12
IND	12
LAX	12
IAH	12

[
  {
    "destination": "DFW",
    "carrier_count": 13
  },
  {
    "destination": "ATL",
    "carrier_count": 12
  },
  {
    "destination": "IND",
    "carrier_count": 12
  },
  {
    "destination": "LAX",
    "carrier_count": 12
  },
  {
    "destination": "IAH",
    "carrier_count": 12
  }
]

SELECT 
   flights."destination" as "destination",
   COUNT(DISTINCT carriers_0."code") as "carrier_count"
FROM '../data/flights.parquet' as flights
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON carriers_0."code"=flights."carrier"
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Join Example

This example demonstrates the definition of several different joins in a model and their use in a query. Entire subtrees of data can be joined. In the example below, aircraft joins aircraft_models, and flights joins aircraft and airports. The tree nature of the join relationship retained.

group_by: aircraft.aircraft_models.manufacturer

source: aircraft_models is duckdb.table('../data/aircraft_models.parquet') extend {
  primary_key: aircraft_model_code
  measure: aircraft_model_count is count()
}

/* Individual airplanes */
source: aircraft is duckdb.table('../data/aircraft.parquet') extend {
  primary_key: tail_num
  measure: aircraft_count is count()
  join_one: aircraft_models with aircraft_model_code
}

/* The airports that the aircraft fly to and from */
source: airports2 is duckdb.table('../data/airports.parquet') extend {
  primary_key: code
  measure: airport_count is count()
}

source: flights4 is duckdb.table('../data/flights.parquet') extend {
  join_one: origin_airport is airports2 with origin
  join_one: destination_airport is airports2 with destination
  join_one: aircraft with tail_num
}

run: flights4 -> {
  group_by: aircraft.aircraft_models.manufacturer
  aggregate:
    flight_count is count()
    aircraft_count is aircraft.count()
    aircraft_model_count is aircraft.aircraft_models.count()
}

QUERY RESULTS

manufacturer	flight_count	aircraft_count	aircraft_model_count
BOEING	183,236	23	17
AIRBUS INDUSTRIE	55,994	8	5
MCDONNELL DOUGLAS	39,106	5	2
EMBRAER	21,799	7	5
AIRBUS	7,699	3	2

[
  {
    "manufacturer": "BOEING",
    "flight_count": 183236,
    "aircraft_count": 23,
    "aircraft_model_count": 17
  },
  {
    "manufacturer": "AIRBUS INDUSTRIE",
    "flight_count": 55994,
    "aircraft_count": 8,
    "aircraft_model_count": 5
  },
  {
    "manufacturer": "MCDONNELL DOUGLAS",
    "flight_count": 39106,
    "aircraft_count": 5,
    "aircraft_model_count": 2
  },
  {
    "manufacturer": "EMBRAER",
    "flight_count": 21799,
    "aircraft_count": 7,
    "aircraft_model_count": 5
  },
  {
    "manufacturer": "AIRBUS",
    "flight_count": 7699,
    "aircraft_count": 3,
    "aircraft_model_count": 2
  }
]

SELECT 
   aircraft_models_0."manufacturer" as "manufacturer",
   COUNT( 1) as "flight_count",
   COUNT(DISTINCT aircraft_0."tail_num") as "aircraft_count",
   COUNT(DISTINCT aircraft_models_0."aircraft_model_code") as "aircraft_model_count"
FROM '../data/flights.parquet' as flights4
 LEFT JOIN '../data/aircraft.parquet' AS aircraft_0
  ON aircraft_0."tail_num"=flights4."tail_num"
 LEFT JOIN '../data/aircraft_models.parquet' AS aircraft_models_0
  ON aircraft_models_0."aircraft_model_code"=aircraft_0."aircraft_model_code"
GROUP BY 1
ORDER BY 2 desc NULLS LAST

For more examples and how to reason about aggregation across joins, review the Aggregates section.

Inner Joins

Inner join are essentially left joins with an additional condition that the parent table has matches in the joined table. The example below functions logically as an INNER JOIN, returning only users that have at least one row in the orders table, and only orders that have an associated user.

source: users4 is duckdb.table('../data/users.parquet') extend {
  join_many: orders is duckdb.table('../data/order_items.parquet') on id = orders.user_id
  where: orders.user_id != null
}

Joins from Queries

Queries may be used as a source for joins, either as-is or extended. They may be referenced by name (as in the example below) or written in-line.

query: aircraft_facts is duckdb.table('../data/flights.parquet') -> {
  group_by: tail_num
  aggregate:
    lifetime_flights is count()
    lifetime_distance is distance.sum()
}

source: flights5 is duckdb.table('../data/flights.parquet') extend {
  join_one: aircraft_facts on tail_num = aircraft_facts.tail_num
}

run: flights5 -> {
  group_by: carrier
  nest: top_aircraft is {
    group_by: tail_num, aircraft_facts.lifetime_flights
    limit: 5
    order_by: lifetime_flights desc
  }
}

QUERY RESULTS

carrier

top_aircraft

tail_num	lifetime_flights
N499AA	7,977
N583AA	7,930
N216AA	7,679
N434AA	7,067
N304AA	2,289

tail_num	lifetime_flights
N974AS	8,453

tail_num	lifetime_flights
N533JB	4,393
N624JB	449

tail_num	lifetime_flights
N47332	7,139

tail_num	lifetime_flights
N242DL	10,788
N171DZ	8,134
N313DL	7,867
N752AT	4,995
N720DA	346

[
  {
    "carrier": "AA",
    "top_aircraft": [
      {
        "tail_num": "N499AA",
        "lifetime_flights": 7977
      },
      {
        "tail_num": "N583AA",
        "lifetime_flights": 7930
      },
      {
        "tail_num": "N216AA",
        "lifetime_flights": 7679
      },
      {
        "tail_num": "N434AA",
        "lifetime_flights": 7067
      },
      {
        "tail_num": "N304AA",
        "lifetime_flights": 2289
      }
    ]
  },
  {
    "carrier": "AS",
    "top_aircraft": [
      {
        "tail_num": "N974AS",
        "lifetime_flights": 8453
      }
    ]
  },
  {
    "carrier": "B6",
    "top_aircraft": [
      {
        "tail_num": "N533JB",
        "lifetime_flights": 4393
      },
      {
        "tail_num": "N624JB",
        "lifetime_flights": 449
      }
    ]
  },
  {
    "carrier": "CO",
    "top_aircraft": [
      {
        "tail_num": "N47332",
        "lifetime_flights": 7139
      }
    ]
  },
  {
    "carrier": "DL",
    "top_aircraft": [
      {
        "tail_num": "N242DL",
        "lifetime_flights": 10788
      },
      {
        "tail_num": "N171DZ",
        "lifetime_flights": 8134
      },
      {
        "tail_num": "N313DL",
        "lifetime_flights": 7867
      },
      {
        "tail_num": "N752AT",
        "lifetime_flights": 4995
      },
      {
        "tail_num": "N720DA",
        "lifetime_flights": 346
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT 
     base."tail_num" as "tail_num",
     COUNT( 1) as "lifetime_flights",
     COALESCE(SUM(base."distance"),0) as "lifetime_distance"
  FROM '../data/flights.parquet' as base
  GROUP BY 1
)
, __stage1 AS (
  SELECT
    group_set,
    flights5."carrier" as "carrier__0",
    CASE WHEN group_set=1 THEN
      flights5."tail_num"
      END as "tail_num__1",
    CASE WHEN group_set=1 THEN
      aircraft_facts_0."lifetime_flights"
      END as "lifetime_flights__1"
  FROM '../data/flights.parquet' as flights5
   LEFT JOIN __stage0 AS aircraft_facts_0
    ON flights5."tail_num"=aircraft_facts_0."tail_num"
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2,3,4
)
SELECT
  "carrier__0" as "carrier",
  COALESCE(LIST({
    "tail_num": "tail_num__1", 
    "lifetime_flights": "lifetime_flights__1"}  ORDER BY  "lifetime_flights__1" desc NULLS LAST) FILTER (WHERE group_set=1)[1:5],[]) as "top_aircraft"
FROM __stage1
GROUP BY 1
ORDER BY 1 asc NULLS LAST