【BigQuery】BY NAME と CORRESPONDING でカラム名ベースの集合演算が可能になりました

Google Cloud データエンジニアのはんざわです。

先日の BigQuery のアップデートで BY NAME と CORRESPONDING の構文が GA になりました。

You can now use the BY NAME and CORRESPONDING modifiers with set operations to match columns by name instead of by position. This feature is generally available (GA).

February 03, 2025

これまで UNION などの集合演算子を使う場合、カラムの位置を合わせる必要がありました。しかし、新しく追加された BY NAME と CORRESPONDING を使うことで、カラム名に基づいて自動的にマッチングできるようになりました。
個人的に興味深いアップデート内容でしたので、さっそく試してみたいと思います。

本ブログは、以下のドキュメントを参考にしています。

参考：BY NAME or CORRESPONDING

BY NAME とは？

まずは、BY NAME の基本的な動作を紹介します。
BY NAME を使うことで、カラムの順番に関係なく、カラム名で自動的にマッチングし、集合演算を行うことができます。
これまで、UNION などの集合演算子はカラムの位置を合わせる必要がありました。
例として、以下のクエリは id と fruits のカラムの順番が異なるため、エラーになります。

WITH sample_1 AS (
  SELECT
    1 AS id, 'apple' AS fruits
),
sample_2 AS (
  SELECT
    'banana' AS fruits, 2 AS id
)

SELECT *
FROM
  sample_1
UNION ALL
SELECT *
FROM
  sample_2

> Column 1 in UNION ALL has incompatible types: INT64, STRING

ここで新たに追加された BY NAME を使用することで、順番が異なってもカラム名で判定させることができます。
使い方はとても簡単で、UNION などの集合演算子の後に BY NAME を追加するだけです。これにより、カラムの順番が異なっていても、カラム名に基づいてデータが統合されます。

WITH sample_1 AS (
  SELECT
    1 AS id, 'apple' AS fruits
),
sample_2 AS (
  SELECT
    'banana' AS fruits, 2 AS id
)

SELECT *
FROM
  sample_1
UNION ALL BY NAME
SELECT *
FROM
  sample_2

 /*--------------*
 | id | fruits   |
 +--------------+
 |  1 | apple    |
 |  2 | banana   |
 *--------------*/

上記の例では、カラムの数が少ないため、あまりメリットがないように感じられますが、カラム数が多い場合には有用ではないかと思われます。

問題点

しかし、BY NAME には 「カラム名が完全に一致している必要がある」 という制約があります。つまり、片方のテーブルでカラム名が異なっていると、エラーになってしまいます。
例えば、以下のように sample_1 の id カラムを user_id に変更すると、エラーになります。

WITH sample_1 AS (
  SELECT
    1 AS user_id, 'apple' AS fruits
),
sample_2 AS (
  SELECT
    'banana' AS fruits, 2 AS id
)

SELECT *
FROM
  sample_1
UNION ALL BY NAME
SELECT *
FROM
  sample_2

BY NAME requires all input queries to have identical column names, but query 1 has [user_id, fruits] and query 2 has [fruits, id]

そこで、BY NAME にはいくつかのオプションが用意されており、より柔軟な制御が可能になっています。
次の章では、BY NAME で利用できるオプションを試してみます。

オプションを試してみる

BY NAME で利用可能なオプションは、以下の表の通りです。

これらのオプションは、JOIN の動作と似ています。例えば、INNER は INNER JOIN、FULL は FULL OUTER JOIN、LEFT は LEFT JOIN に近い動作をします。
そのため、JOIN に慣れている方は、同じ感覚で利用できるでしょう。

INNER

INNER を <集合演算子> BY NAME の前に付け加えることで、両方のテーブルに共通するカラムのみが出力されます。つまり、片方のテーブルにしか存在しないカラムは、結果から除外されます。

以下の例では、sample_1 と sample_2 の両方のテーブルにある fruits のカラムのみ出力されています。

WITH sample_1 AS (
  SELECT
    1 AS user_id, 'apple' AS fruits
),
sample_2 AS (
  SELECT
    'banana' AS fruits, 2 AS id
)

SELECT *
FROM
  sample_1
INNER UNION ALL BY NAME
SELECT *
FROM
  sample_2

 /*-------*
 | fruits |
 +--------+
 | apple  |
 | banana |
 *-------*/

FULL

FULL を <集合演算子> BY NAME の前に付け加えることで、両方のテーブルにある全てのカラムが出力されます。片方のテーブルにのみ存在するカラムは NULL で補完されます。

以下の例では、sample_1 と sample_2 の両方のテーブルの全てのカラム（user_id, fruits, id）が出力されています。user_id と id は、それぞれ片方のテーブルにのみ存在するため、NULL で補完されています。

WITH sample_1 AS (
  SELECT
    1 AS user_id, 'apple' AS fruits
),
sample_2 AS (
  SELECT
    'banana' AS fruits, 2 AS id
)

SELECT *
FROM
  sample_1
FULL UNION ALL BY NAME
SELECT *
FROM
  sample_2

 /*------------------------*
 | user_id | fruits |  id  |
 +-------------------------+
 |    1    | apple  | NULL |
 |  NULL   | banana |   2  |
 *------------------------*/

LEFT

LEFT を <集合演算子> BY NAME の前に付け加えることで、左のテーブルにある全てのカラムが出力されます。左のテーブルのカラムが右のテーブルに存在しない場合、NULL で補完されます。

以下の例では、sample_1 の左テーブルにある全てのカラム（user_id, fruits）が出力されています。sample_2 の右テーブルに user_id カラムは存在しないため、NULL で補完されています。

WITH sample_1 AS (
  SELECT
    1 AS user_id, 'apple' AS fruits
),
sample_2 AS (
  SELECT
    'banana' AS fruits, 2 AS id
)

SELECT *
FROM
  sample_1
LEFT UNION ALL BY NAME
SELECT *
FROM
  sample_2

 /*------------------*
 | user_id | fruits |
 +------------------+
 |    1    | apple  |
 |   NULL  | banana |
 *-----------------*/

ON (col1, col2, ...)

BY NAME の後ろに ON (col1, col2, ...) をを付け加えることで、出力するカラムとその順序を明示的に指定することができます。また、これまでに紹介した LEFT や FULL などと一緒に使用することも可能です。

以下の例では、user_id と fruits のカラムを出力するように指定しています。FULL を使用しているため、本来であれば全てのカラムが出力されますが、指定したカラムのみが出力されています。

WITH sample_1 AS (
  SELECT
    1 AS user_id, 'apple' AS fruits
),
sample_2 AS (
  SELECT
    'banana' AS fruits, 2 AS id
)

SELECT *
FROM
  sample_1
FULL UNION ALL BY NAME ON (user_id, fruits)
SELECT *
FROM
  sample_2

 /*-----------------*
 | user_id | fruits |
 +------------------+
 |    1    | apple  |
 |  NULL   | banana |
 *-----------------*/

CORRESPONDING とは？

CORRESPONDING を使うと、両方のテーブルに共通するカラムのみを自動的に選択できます。
これは INNER <集合演算子> BY NAME と同じ動作をします。
UNION ALL CORRESPONDING のように、集合演算子の後ろに付け加えることで利用できます。

WITH sample_1 AS (
  SELECT
    1 AS user_id, 'apple' AS fruits
),
sample_2 AS (
  SELECT
    'banana' AS fruits, 2 AS id
)

SELECT *
FROM
  sample_1
UNION ALL CORRESPONDING
SELECT *
FROM
  sample_2

以下の結果の通り、INNER UNION ALL BY NAME と同じ結果が出力されていることがわかります。

 /*-------*
 | fruits |
 +--------+
 | apple  |
 | banana |
 *-------*/

さらに、STRICT CORRESPONDING とすることで BY NAME と同じ動作をします。どちらを使っても同じ結果が得られます。

WITH sample_1 AS (
  SELECT
    1 AS id, 'apple' AS fruits
),
sample_2 AS (
  SELECT
    'banana' AS fruits, 2 AS id
)

SELECT *
FROM
  sample_1
UNION ALL STRICT CORRESPONDING
SELECT *
FROM
  sample_2

以下の結果の通り、UNION ALL BY NAME と同じ結果が出力されていることがわかります。

 /*--------------*
 | id | fruits   |
 +--------------+
 |  1 | apple    |
 |  2 | banana   |
 *--------------*/

このように CORRESPONDING は書き方次第で BY NAME と同等の結果を取得することができます。
以下の表は、BY NAME と同等の CORRESPONDING の表記をまとめたものです。

BY NAME と STRICT CORRESPONDING は同じ動作をしますが、BY NAME の方が短くてシンプルなため、基本的にはこちらの使用を推奨しているようです。

まとめ

今回のブログでは、先日のアップデートで正式に GA になった BY NAME と CORRESPONDING について紹介しました。
これらの機能を活用することで、集合演算子をカラムの順番ではなく名前で一致させることができ、よりシンプルで可読性の高いクエリを書くことが可能になります。

また、これまで LEFT JOIN などを使って左右のテーブルを結合していたのと同じ感覚で、BY NAME を使うことで、上下のテーブルの結合もより柔軟で直感的に行えるようになったと思います。

ぜひ、実際に試してみてください！