title: Performance Comparison: Full outer join vs Union, Union All
body: <p>I have tables named:</p>
  
  <pre><code>mktActualsales (SaleID, EmployeeID, PeriodID,PositionID)
   mktActualSalesItems(SaleItemID, saleID, Item, Quantity)
   mktSalesTargets(TargetID, EmployeeID, PeriodID,PositionID)
   mktSalesTargetItems(TargetITemID, TargetID,ItemID, Quantity )
   sysPeriods(PeriodID, StartDate, EndDate) 
  </code></pre>
  
  <p>The relationships are obvious among above tables sale and saleDetails are related through SaleID and Target and TargetDetail are related through TargetID. What I want to display is Employees sales and targets in all periods. There are periods where sale is missing and in other periods targets might be missing.</p>
  
  <p>My plan of doing it (so far) is to take inner join of sale and saleDetail in one subquery, target and targetDetail in another subquery and then full outer join on both subqueries get me the data I need. But the result is awfully slow. what i can do to speed up performance. I have Googled around for performance comparison between full outer join and union all but did not have any luck. At the moment I have no idea if result I want can be achieved using Union/Union All.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>This is the query that I have so far. Edited the tables as well</p>
  
  <pre><code>SELECT  sale.ActualSaleID, 
          COALESCE (sale.EmployeeID, saleTarget.EmployeeID) AS EmployeeID, 
          COALESCE (sale.PositionID, saleTarget.PositionID) AS PositionID, 
          COALESCE (sale.PeriodID, saleTarget.PeriodID) AS PeriodID, 
          COALESCE (sale.SKUID, saleTarget.SKUID) AS SKUID, 
          COALESCE (sale.SalesQuantity, 0) AS SalesQuantity, 
          saleTarget.SalesTargetID, 
          COALESCE (saleTarget.TargetQuantity, 0) AS TargetQuantity, 
          saleTarget.SalesTargetItemID, 
          sale.ActualSaleItemID, 
          p.StartDate, 
          p.EndDate
  FROM (
          SELECT  s.ActualSaleID, 
                  s.EmployeeID, 
                  s.PeriodID, 
                  s.PositionID, 
                  si.ActualSaleItemID, 
                  si.SKUID, 
                  si.SalesQuantity
          FROM dbo.mktActualSaleItems AS si 
              INNER JOIN dbo.mktActualSales AS s 
                  ON si.ActualSaleID = s.ActualSaleID
      ) AS sale 
      FULL OUTER JOIN
      (
          SELECT  t.EmployeeID, 
                  t.PeriodID, 
                  t.PositionID, 
                  t.SalesTargetID, 
                  ti.SKUID, 
                  ti.TargetQuantity, 
                  ti.SalesTargetItemID
          FROM dbo.mktSalesTargetItems AS ti 
              INNER JOIN dbo.mktSalesTargets AS t 
                  ON t.SalesTargetID = ti.SalesTargetID
      ) AS saleTarget 
          ON sale.PeriodID = saleTarget.PeriodID 
          AND sale.PositionID = saleTarget.PositionID 
          AND sale.SKUID = saleTarget.SKUID 
      INNER JOIN dbo.sysPeriods AS p 
          ON p.PeriodID = COALESCE (sale.PeriodID, saleTarget.PeriodID)
  </code></pre>