title: Use "show tables" as a derived table
body: <p>I'm trying to combine &quot;<code>show tables like</code>&quot; as a derived table in a join.</p>
  <p>This works:</p>
  <pre><code>  show tables like 'table_name_pattern_%'
  </code></pre>
  <p>However I cannot figure out how to make this work</p>
  <pre><code>  select i.* from table i
    left join (
        show tables like 'table_name_pattern_%'
    ) a on i.id=mid(a.`columnname_from_show_tables`, 20)
  </code></pre>
  <p>Any ideas, or is show tables a special statement that can't be joined?</p>
  <p>I'm on MySQL 5.7</p>