-1

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.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
adin
  • 783
  • 3
  • 13
  • 27
  • 1
    You only want rows where `common` starts with one of the given strings? Or you want all rows, but only join the B rows when `common` starts with one of the given strings? – Thorsten Kettner Jan 24 '22 at 18:42
  • The first, where only `common` starts with one of the given strings in the list. – adin Jan 24 '22 at 18:45

2 Answers2

2

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')
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
  • @adin: While this is a good answer on how to only outer join rows matching the given strings, you said in your request comments explicitly that this is what you don't want. – Thorsten Kettner Jan 24 '22 at 18:53
1

In the request comments you have clarified that you only want to select rows that match the given strings. In that case apply the SUBSTR function on b.common, not a.common:

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(b.common, 1, 5) IN ('90210', '90211', '90212');
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73