PySpark中如何按不同连接键关联DataFrame并筛选指定列？

Hey there! Let's get this sorted out for you. The issue with your initial code might be a small mix-up in the join keys (you used A.id == B.id instead of A.a_id == B.b_id) or how you're referencing columns in the select step. Here are a few clean, straightforward alternatives to the SQL approach you already know:

1. Corrected DataFrame API Approach #

This is the direct fix to your original code—just make sure you're using the correct join keys and properly referencing columns:

# Specify the join condition correctly, then select all columns from A + B's b1/b2
A_B = A.join(B, A.a_id == B.b_id) \
       .select(A["*"], B.b1, B.b2)

You can also explicitly define the join type (like how="left" for left join) if needed—by default it's an inner join.

2. Use Aliases for Cleaner Code #

If your DataFrames have longer names or you want to avoid ambiguity, using aliases makes the code more readable:

# Assign aliases to your DataFrames
df_a = A.alias("A")
df_b = B.alias("B")

# Join and select using alias references
A_B = df_a.join(df_b, df_a.a_id == df_b.b_id) \
         .select("A.*", "B.b1", "B.b2")

This is especially helpful if there are overlapping column names (other than the join keys) in your DataFrames.

3. SelectExpr for Concise Syntax #

Another option is using selectExpr, which lets you write column references in a SQL-like string format:

A_B = A.join(B, A.a_id == B.b_id) \
       .selectExpr("A.*", "B.b1", "B.b2")

This works exactly like the select method but can feel more intuitive if you're used to SQL syntax.

All these methods will give you the same result as your SQL query—just pick the one that fits your coding style best!

内容的提问来源于stack exchange，提问作者ASU_TY