SparkSQL and Hive unnest examples.

inner_join_vs_explode.sql

-- REGULAR
WITH
    ORDERS(order_id, dt) AS (
      VALUES
          (1, DATE('2022-01-01')),
          (2, DATE('2022-01-02')),
          (3, DATE('2022-01-03'))
    ),
    ITEMS(item_id, order_id, price) AS (
      VALUES
          (1, 1, 1.1),
          (2, 1, 2.2),
          (3, 2, 3.3)
    )
SELECT order_id, item_id, price, dt
FROM ORDERS
INNER JOIN ITEMS USING(order_id);

-- NESTED
WITH
    ORDERS(order_id, dt, items) AS (
      VALUES
        (
            1, DATE('2022-01-01'), ARRAY(
                NAMED_STRUCT('item_id', 1, 'price', 1.1),
                NAMED_STRUCT('item_id', 2, 'price', 2.2)
            )
        ),
        (
            2, DATE('2022-01-02'), ARRAY(
                NAMED_STRUCT('item_id', 3, 'price', 3.3)
            )
        ),
        (
            2, DATE('2022-01-03'), NULL
        )
    )
SELECT order_id, ITEM.item_id, ITEM.price, dt
FROM ORDERS
LATERAL VIEW EXPLODE (ORDERS.items) as ITEM;

left_join_vs_outer_explode.sql

-- REGULAR
WITH
    ORDERS(order_id, dt) AS (
      VALUES
          (1, DATE('2022-01-01')),
          (2, DATE('2022-01-02')),
          (3, DATE('2022-01-03'))
    ),
    ITEMS(item_id, order_id, price) AS (
      VALUES
          (1, 1, 1.1),
          (2, 1, 2.2),
          (3, 2, 3.3)
    )
SELECT order_id, item_id, price, dt
FROM ORDERS
LEFT JOIN ITEMS USING(order_id);

-- NESTED
WITH
    ORDERS(order_id, dt, items) AS (
      VALUES
        (
            1, DATE('2022-01-01'), ARRAY(
                NAMED_STRUCT('item_id', 1, 'price', 1.1),
                NAMED_STRUCT('item_id', 2, 'price', 2.2)
            )
        ),
        (
            2, DATE('2022-01-02'), ARRAY(
                NAMED_STRUCT('item_id', 3, 'price', 3.3)
            )
        ),
        (
            2, DATE('2022-01-03'), NULL
        )
    )
SELECT order_id, ITEM.item_id, ITEM.price, dt
FROM ORDERS
LATERAL VIEW OUTER EXPLODE (ORDERS.items) as ITEM;