我认为您需要优先级查询-从b中选择所有内容，然后从a中选择不匹配的行：

select b.id,b.name,b.p
from b
union all
select a.id,a.name,a.p
from a
where not exists (select 1 from b where b.id = a.id);

我会尝试这样做，因为您不能依赖p的值：

WITH                                                                                                                                                    
-- your input starts here
a(id,name,p) AS (
          SELECT 1,'abc','A'
UNION ALL SELECT 2,'bcd','A'
UNION ALL SELECT 3,'cde','A'
),b(id,p) AS (
          SELECT 3,'ccc','A'
UNION ALL SELECT 4,'ddd','A'
UNION ALL SELECT 5,'eee','A'
)
-- your input ends here
-- replace the comma below with "WITH",combi AS (
SELECT
  1 AS tb_priority,*
FROM a
UNION ALL
SELECT
  2 AS tb_priority,*
FROM b
)
SELECT
  id,p
FROM combi u
WHERE tb_priority = (
  SELECT MAX(tb_priority)
  FROM combi c
  WHERE c.id = u.id
)
;                                                                                                                                                       

  id | name | p 
 ----+------+---
   1 | abc  | A
   4 | ddd  | A
   2 | bcd  | A
   5 | eee  | A
   3 | ccc  | A

这是我用的

想法是创建一个伪列，对于表A ，它将为==> NULL ，并具有==> 名称从表B中选择非空值作为final_name

已执行Sql

 val resultSql = """ select sub.id,sub.p,max(nvl(sub.pseudo_name,sub.name)) as result_name 
                              from 
                             (
                               select a.id,a.p,null  as pseudo_name from a 
                             union all 
                               select b.id,b.p,b.name as pseudo_name  from b ) sub 
                     group by 1,2 order by 1"""

完整代码 =>

val table1 = Seq((1,"abc","A"),(2,"bcd",(3,"first_table_value","A")).toDF("id","name","p")
val table2 = Seq((3,"second_table_value",(4,"ddd",(5,"eee","p")
table1.createOrReplaceTempView("A")
table2.createOrReplaceTempView("B")
val resultSql = """ select sub.id,sub.name)) as result_name from (select a.id,null  as pseudo_name from a union all  select b.id,b.name as pseudo_name  from b ) sub group by 1,2 order by 1"""
spark.sql(resultSql).show(false)

和输出 =>

|id |p  |result_name       |
+---+---+------------------+
|1  |A  |abc               |
|2  |A  |bcd               |
|3  |A  |second_table_value|
|4  |A  |ddd               |
|5  |A  |eee               |
+---+---+------------------+