SQL LEFT JOIN with WHERE and SUBSTR

January 24, 2022

I want to use SQL to left join two tables but only where the common column fits a substring criteria. How can this be accomplished?

CREATE TABLE MY_JOIN AS
    SELECT A.*, B.*
FROM DATA.TABLE_B AS B
LEFT JOIN DATA.TABLE_A AS A ON A.COMMON = B.COMMON
WHERE SUBSTR(A.COMMON,1,5) IN ('90210', '90211', '90212')

There are other questions that address using a left join with a where clause but do not specifically address using a substring function within the where clause.

>Solution :

Simply move the condition to the LEFT JOIN clause

CREATE TABLE MY_JOIN AS
    SELECT A.*, B.*
FROM DATA.TABLE_B AS B
LEFT JOIN DATA.TABLE_A AS A ON A.COMMON = B.COMMON 
                            and SUBSTR(A.COMMON,1,5) IN ('90210', '90211', '90212')