title: Conditional JOIN in Apache Beam SqlTransform Python SDK
body: <p>I cannot find the working syntax in SqlTransform for left joining two PCollections. My code is (Python SDK):</p>
  <pre><code>class Products(typing.NamedTuple):
          product: str
          name: str
          code3: str
          
      class Groups(typing.NamedTuple):
          code1: str
          name1: str
          code2: str
          name2: str
          code3: str
          name3: str
      beam.coders.registry.register_coder(Products, beam.coders.RowCoder)
      beam.coders.registry.register_coder(Groups, beam.coders.RowCoder)
  
      with beam.Pipeline() as p:
      
          products = p | 'ReadProducts' &gt;&gt; beam.io.ReadFromText(data_path + 'products.csv', skip_header_lines=1)\
                       | beam.Map(lambda line: line.split(';'))\
                       | beam.Map(lambda x: beam.Row(product = str(x[0]), name = str(x[1]), code3 = str(x[2])))#.with_output_types(Products)
          
         
          groups = p | 'ReadGroups' &gt;&gt; beam.io.ReadFromText(data_path + 'groups.csv', skip_header_lines=1)\
                       | beam.Map(lambda line: line.split(';'))\
                       | beam.Map(lambda x: beam.Row(code1 = str(x[0]), name1 = str(x[1]), code2 = str(x[2]), name2 = str(x[3]), code3 = str(x[4]), name3 = str(x[5])))#.with_output_types(Groups)
          # JOIN
          dict_ = {'products':products, 'groups':groups}
          dict_ | SqlTransform(&quot;&quot;&quot;
                    SELECT *
                    FROM products LEFT JOIN groups ON products.code3 = groups.code3;&quot;&quot;&quot;)
  </code></pre>
  <blockquote>
  <p>The error:
  --------------------------------------------------------------------------- RuntimeError                              Traceback (most recent call
  last)  in 
  18     # JOIN
  19     dict_ = {'products':products, 'groups':groups}
  ---&gt; 20     dict_ | SqlTransform(&quot;&quot;&quot;
  21               SELECT *
  22               FROM products LEFT JOIN groups ON products.code3 = groups.code3;&quot;&quot;&quot;)</p>
  <p>~/HDD2/WORK/PROJECTS/Apache_Beam/env/lib/python3.8/site-packages/apache_beam/transforms/ptransform.py
  in <strong>ror</strong>(self, left, label)
  570     pvalueish = _SetInputPValues().visit(pvalueish, replacements)
  571     self.pipeline = p
  --&gt; 572     result = p.apply(self, pvalueish, label)
  573     if deferred:
  574       return result</p>
  <p>................</p>
  <p>at java.lang.Thread.run(Thread.java:748) Caused by: o</p>
  <p>rg.apache.beam.vendor.calcite.v1_20_0.org.apache.calcite.sql.parser.SqlParseException:
  Encountered &quot;LEFT JOIN groups&quot; at line 3, column 29. Was expecting one
  of:
  &quot;NATURAL&quot; ...
  &quot;JOIN&quot; ...
  &quot;INNER&quot; ...
  &quot;LEFT&quot; &quot;OUTER&quot; ...
  &quot;LEFT&quot; &quot;JOIN&quot; ...
  &quot;LEFT&quot; &quot;JOIN&quot;  ...
  &quot;LEFT&quot; &quot;JOIN&quot; &lt;QUOTED_IDENTIFIER&gt; ...
  &quot;LEFT&quot; &quot;JOIN&quot; &lt;BACK_QUOTED_IDENTIFIER&gt; ...
  &quot;LEFT&quot; &quot;JOIN&quot; &lt;BRACKET_QUOTED_IDENTIFIER&gt; ...
  &quot;LEFT&quot; &quot;JOIN&quot; &lt;UNICODE_QUOTED_IDENTIFIER&gt; ...
  &quot;LEFT&quot; &quot;JOIN&quot; &quot;LATERAL&quot; ...
  &quot;LEFT&quot; &quot;JOIN&quot; &quot;(&quot; ...
  &quot;LEFT&quot; &quot;JOIN&quot; &quot;UNNEST&quot; ...
  &quot;LEFT&quot; &quot;JOIN&quot; &quot;TABLE&quot; ...
  &quot;,&quot; ...
  &quot;CROSS&quot; ...
  &quot;OUTER&quot; ...
  &quot;EXTEND&quot; ...
  &quot;(&quot; ...</p>
  </blockquote>