Lets introduce billing table which is also partitioned
CREATE TABLE billing
PRIMARY KEY (billing_id, country),
FOREIGN KEY (order_id, country) REFERENCES orders (order_id, country)
) PARTITION BY LIST (country);
CREATE TABLE billing_de PARTITION OF billing FOR VALUES IN ('de');
CREATE TABLE billing_in PARTITION OF billing FOR VALUES IN ('in');
For partition-wise joins to work (as intended), they should be
equi-partitioned. For example, for the below query - the join will
find matching partitions based on bounds. In this case, the join
pairs are orders_de, billing_de and orders_in, billing_in.
But if billing table had another partition billing_us, it will
not be paired with anything.
SELECT * FROM orders
JOIN billing b ON orders.order_id = b.order_id AND orders.country = b.country;