22. Cross joins, self-joins, and ambiguous columns

Three join situations that trip people up.

Cross join — every combination

A cross join pairs every left row with every right row (a Cartesian product). Useful for generating all combinations — e.g. every segment × every category, to left-join actuals onto later:

segments = customers.select("segment").distinct()
categories = orders.select("category").distinct()
segments.crossJoin(categories).show()

Rows multiply fast (n × m) — a cross join of two big tables will melt a cluster. It's deliberate, and you must call crossJoin explicitly.

Self-join — a table against itself

Join a DataFrame to itself to compare rows within it. To keep the two copies distinct, give each an alias and reference columns through it. Here: pairs of customers in the same country.

c1 = customers.alias("c1")
c2 = customers.alias("c2")
c1.join(
    c2,
    (F.col("c1.country") == F.col("c2.country")) & (F.col("c1.customer_id") < F.col("c2.customer_id")),
).select(
    F.col("c1.name").alias("customer_a"),
    F.col("c2.name").alias("customer_b"),
    F.col("c1.country").alias("country"),
).show(6)

The c1.customer_id < c2.customer_id condition keeps each pair once (and drops self-pairs).

Ambiguous columns after a join

Both orders and customers have a country column. After joining, which country does a bare "country" mean? On a real Spark cluster this raises AnalysisException: Reference 'country' is ambiguous. The fix — which works everywhere — is to reference the column through its DataFrame and alias it:

o = orders.dropDuplicates()
j = o.join(customers, "customer_id")
j.select(
    "order_id",
    o["country"].alias("ship_country"),
    customers["country"].alias("home_country"),
).show(6)

Engine note. The in-browser engine is lenient — a bare "country" here just picks one side instead of erroring. Don't rely on that: always disambiguate with df["col"] + alias, and your code is correct on a real cluster too.

Your turn

Join deduplicated orders to customers and return order_id, the order's shipping country (from orders) as ship_country, and the customer's home country (from customers) as home_country. Assign to result.

o = orders.dropDuplicates()
result = o  # <- join customers on customer_id; select order_id, o["country"] AS ship_country, customers["country"] AS home_country
result.show()

o = orders.dropDuplicates()
result = o.join(customers, "customer_id").select(
    "order_id",
    o["country"].alias("ship_country"),
    customers["country"].alias("home_country"),
)