Alternative to 'OR' condition in SQL 'JOIN'

Question

Problem: I have a Sub-optimal query resulting in (what I consider to be) a high querying time.

Detail: I have a number of large tables (rows in the hundreds of thousands / millions) that require cross referencing to obtain the info required - I'm using 'JOINs' to perform this. The final JOIN I'm performing contains several 'OR' conditions within the 'ON' clause. I am working within 'Snowflake' SQL query language.

Resolution Required: Looking to find a more optimal way of performing the following query:

SELECT
     table1.col_a
    ,table1.col_b
    ,table2.col_1
    ,table2.col_2
    ,table3.col_x
    ,table3.col_y
    ,table3.col_z
FROM table1
JOIN table2 ON table1.col_a = table2.col_1
JOIN table3 ON table1.col_b LIKE ANY (table3.col_x, table3.col_y, table3.col_z)

Options considered: As I'm assuming the time taken is in the search required for table1.col_b within a number of columns 'x', 'y' and 'z' for table3, my current approach is to concatenate the 3 columns in a sub-query and search just one column of table 3 then.

Looking to understand if there's any other suggestions people might have?

Answer 1

You can write :

SELECT table1.col_a
      ,table1.col_b
      ,table2.col_1
      ,table2.col_2
      ,table3.col_x
      ,table3.col_y
      ,table3.col_z
FROM  table1
      JOIN table2 
         ON table1.col_a = table2.col_1
      JOIN table3 
         ON table1.col_b LIKE table3.col_x
UNION 
SELECT table1.col_a
      ,table1.col_b
      ,table2.col_1
      ,table2.col_2
      ,table3.col_x
      ,table3.col_y
      ,table3.col_z
FROM  table1
      JOIN table2 
         ON table1.col_a = table2.col_1
      JOIN table3 
         ON table1.col_b LIKE table3.col_y
UNION
SELECT table1.col_a
      ,table1.col_b
      ,table2.col_1
      ,table2.col_2
      ,table3.col_x
      ,table3.col_y
      ,table3.col_z
FROM  table1
      JOIN table2 
         ON table1.col_a = table2.col_1
      JOIN table3 
         ON table1.col_b LIKE table3.col_z

And if possible replace LIKE by =

Some RDBMS like SQL Server can optimise LIKE with jokers depending of pattern for the joined value, some other cannot !