title: How can I write this query as a full join instead of a union left/right join?
body: <p>here's the code, showing the inputs and the required output.  </p>
  
  <p>Basically, I'm trying to self-join to match the results of my broker's statement with my internal records.  So left set of columns is broker's list, right side is my list.  If broker has a position, and I don't, NULLs on the right.  If I have a position and broker doesn't, NULLs on the left.</p>
  
  <p>The left join + right join + union works exactly as I want.  Seems like there should be some voodoo to allow a full join to get that without two selects, but I can't figure it out.</p>
  
  <pre><code>drop table MatchPositions
  go
  
  create table MatchPositions
  ( 
      mt_source varchar (10),
      mt_symbol varchar (10),
      mt_qty float,
      mt_price float
  )
  
  go
  
  insert into MatchPositions values ('BROKER', 'IBM', 100, 50.25)
  insert into MatchPositions values ('BROKER', 'MSFT', 75, 30)
  insert into MatchPositions values ('BROKER', 'GOOG', 25, 500)
  insert into MatchPositions values ('BROKER', 'SPY', 200, 113)
  
  insert into MatchPositions values ('MODEL', 'MSFT', 75, 30)
  insert into MatchPositions values ('MODEL', 'GOOG', 25, 500)
  insert into MatchPositions values ('MODEL', 'GLD', 300, 150)
  
  go
  
  select * from MatchPositions b
  left join MatchPositions m on b.mt_symbol = m.mt_symbol and m.mt_source = 'MODEL'
  where b.mt_source = 'BROKER'
  union
  
  select * from MatchPositions b
  right join MatchPositions m on b.mt_symbol = m.mt_symbol and b.mt_source = 'BROKER'
  where m.mt_source = 'MODEL'
  </code></pre>
  
  <p>and here's the expected output:</p>
  
  <pre><code>mt_source  mt_symbol  mt_qty                 mt_price               mt_source  mt_symbol  mt_qty                 mt_price
  ---------- ---------- ---------------------- ---------------------- ---------- ---------- ---------------------- ----------------------
  NULL       NULL       NULL                   NULL                   MODEL      GLD        300                    150
  BROKER     GOOG       25                     500                    MODEL      GOOG       25                     500
  BROKER     IBM        100                    50.25                  NULL       NULL       NULL                   NULL
  BROKER     MSFT       75                     30                     MODEL      MSFT       75                     30
  BROKER     SPY        200                    113                    NULL       NULL       NULL                   NULL
  </code></pre>