title: Most performant way to query products from multiple tables and combine results?
body: <p>I essentially have two core categories of products in a system with multiple manufacturers.  The current query in the system was written to handle only one product.</p>
  
  <p>Here is the code:</p>
  
  <pre><code>SELECT DISTINCT c.id
  FROM "Config" c
  JOIN "FirstProduct" fp ON c.id = fp.widget
  JOIN "TheSupplier" ts ON ts.name = fp.manufacturer
  LEFT JOIN "ProductOffer" o ON (o.app = ts.app)
  WHERE ts.app = ?appId
  AND (?mlid IS NULL OR ?mlid = fp.manufacturer)
  AND (o.company IS NULL OR o.manufacturer &lt;&gt; fp.manufacturer)
  </code></pre>
  
  <p>What I want to get to: adding a query for another table, which I think can be done in two ways:</p>
  
  <ol>
  <li>Effectively do both queries in one query</li>
  </ol>
  
  <pre><code>SELECT DISTINCT c.id
  FROM "Config" c
  JOIN "FirstProduct" fp ON c.id = fp.widget fpw
  JOIN "SecondProduct" sp ON c.id = sp.widget spw
  JOIN "TheSupplier" ts ON ts.name = fp.manufacturer
  LEFT JOIN "ProductOffer" o ON (o.app = ts.app)
  WHERE ts.app = ?appId
  AND (?mlid IS NULL OR ?mlid = fp.manufacturer)
  AND (o.company IS NULL OR o.manufacturer &lt;&gt; fp.manufacturer)
  </code></pre>
  
  <ol start="2">
  <li>Do two separate queries, and UNION them.</li>
  </ol>
  
  <pre><code>with first_product_table as (
  SELECT DISTINCT c.id
  FROM "Config" c
  JOIN "FirstProduct" fp ON c.id = fp.widget
  JOIN "TheSupplier" ts ON ts.name = fp.manufacturer
  LEFT JOIN "ProductOffer" o ON (o.app = ts.app)
  WHERE ts.app = ?appId
  AND (?mlid IS NULL OR ?mlid = fp.manufacturer)
  AND (o.company IS NULL OR o.manufacturer &lt;&gt; fp.manufacturer)
  ) ,
  second_product_table as (
  SELECT DISTINCT c.id
  FROM "Config" c
  JOIN "SecondProduct" sp ON c.id = sp.widget spw
  JOIN "TheSupplier" ts ON ts.name = fp.manufacturer
  LEFT JOIN "ProductOffer" o ON (o.app = ts.app)
  WHERE ts.app = ?appId
  AND (?mlid IS NULL OR ?mlid = fp.manufacturer)
  AND (o.company IS NULL OR o.manufacturer &lt;&gt; fp.manufacturer)
  )
  select * from first_product_table
  UNION
  second_product_table
  </code></pre>
  
  <p>This will be in a production system, so performance concerns are non-trivial.</p>