这是否可能使用case语句。。。？
下面是bigquery标准sql的简化/虚拟示例

# standardSQL

WITH `project.dataset.tableA` AS (
  SELECT 1 colsA, 'apple' name union all
  SELECT 2, 'lemon' union all
  SELECT 3, 'peach'
), `project.dataset.tableB` AS (
  SELECT 4 colsB, 'apple' name union all
  SELECT 5, 'orange' union all
  SELECT 6, 'melon'
)
SELECT 
  a.name AS a_name,
  b.name AS b_name,
  colsA,
  colsB
FROM `project.dataset.tableA` a
JOIN `project.dataset.tableB` b
ON CASE 
    WHEN a.name = 'apple' AND b.name IN ('apple', 'orange') THEN TRUE
    WHEN a.name = 'peach' AND b.name IN ('peach', 'melon') THEN TRUE
  END

输出：

Row a_name  b_name  colsA   colsB    
1   apple   apple   1       4    
2   apple   orange  1       5    
3   peach   melon   3       6

... 或者其他方式？
即使是上述的简化版本也是如此

# standardSQL

SELECT 
  a.name AS a_name,
  b.name AS b_name,
  colsA,
  colsB
FROM `project.dataset.tableA` a
JOIN `project.dataset.tableB` b
ON (a.name = 'apple' AND b.name IN ('apple', 'orange'))
OR (a.name = 'peach' AND b.name IN ('peach', 'melon'))

很明显，输出相同

你可以使用更复杂的逻辑 on 条款：

from a join
     b
     on a.name = 'apple and b.name in ('apple', 'orange')

You can make similar WHEN conditions if you have more combinations in B table  
that you would like to join like apple and orange. However if it's only apple 
and orange then this can be done using below.

SELECT A.*,B.* 
FROM tbl_A A 
JOIN tbl_B B on A.name = CASE WHEN B.Name in ('Apple','Orange')
                              THEN 'Apple'
                              ELSE B.Name
                              END

尝试以下方法：

SELECT * FROM A a
    INNER JOIN B b ON a.name = 'Apple' AND b.name IN ('Apple', 'Orange')