sql - Oracle SQL JOIN with OR condition

Question

I would like to confirm if it is possible to make a JOIn with and OR condition like (for me it is not working):

SELECT * FROM table_a
LEFT JOIN table_b
ON table_a.field_1 = table_b.field_1 OR table_a.field_2 = table_b.field_2

I tried it an equivalent query, but it was not running. How can I make it work?

标签： sqloracle

Answer 1

是的，这是可能的——ON子句中允许使用任何布尔表达式，甚至是子查询。

OR通常是性能杀手。

像这样的查询：

SELECT *
FROM table_a a LEFT JOIN
     table_b b
     ON a.field_1 = b.field_1 OR 
        a.field_2 = b.field_2;

经常可以改写为：

SELECT a.*,
       COALESCE(b1.col, b2.col) as col  -- choose the column value from one of the `b` tables
FROM table_a a LEFT JOIN
     table_b b1
     ON a.field_1 = b1.field_1 LEFT JOIN
     table_b b2 
     ON b2.field_2 = b2.field_2 AND
        b1.field_1 IS NULL    -- no match on other column

两个这样的连接通常效率更高。