title: How left joins and right joins work in relational algebra
body: <p>If I have two tables</p>
  
  <pre><code>CREATE TABLE R
  (
  a int,
  b int
  );
  
  CREATE TABLE S
  (
  b int,
  c int,
  d int
  );
  
  INSERT INTO R
  VALUES (3,1),(2,3),(2,0),(3,3),(1,1);
  
  INSERT INTO S
  VALUES (2,2,3),(3,0,0),(1,3,3),(2,2,0);
  </code></pre>
  
  <p>Then I do <code>R outer join S</code>. I'm trying to figure out the result. If I try to use sql code, I would do:</p>
  
  <pre><code>  SELECT r.a,r.b,s.c,s.d
    FROM R r
    LEFT JOIN S s ON r.b=s.b
  
    UNION
  
    SELECT r.a,r.b,s.c,s.d
    FROM R r
    RIGHT JOIN S s ON r.b=s.b
  </code></pre>
  
  <p>The result of that query is:</p>
  
  <pre><code>A   B   C   D
  3   1   3   3
  2   3   0   0
  2   0   -   -
  3   3   0   0
  1   1   3   3
  -   -   2   3
  -   -   2   0
  </code></pre>
  
  <p>If I try to solve it by hand:</p>
  
  <p>The <code>3 1</code> matches with <code>1 3 3</code></p>
  
  <p>The <code>2 3</code> matches with <code>3 0 0</code></p>
  
  <p>The <code>2 0</code> matches with <code>- - -</code></p>
  
  <p>The <code>3 3</code> matches with <code>3 0 0</code></p>
  
  <p>The <code>1 1</code> matches with <code>1 3 3</code></p>
  
  <p>This makes the first 5 rows above in that result. The two remaining rows come from the right join. But what I don't understand is why the right join produces</p>
  
  <pre><code>-   -   2   3
  -   -   2   0
  </code></pre>
  
  <p>Because on the left join the <code>2 0</code> didn't match with anything, yet the <code>b</code> column was preserved and the 0 was there.</p>
  
  <p>But on the right join, the <code>2 2 3</code> and <code>2 2 0</code> didn't match with anything but the b column turned into a null. Shouldn't the b column be preserved here like in the left join?</p>
  
  <p>Can anyone explain whats going on here?</p>
  
  <p>Thanks.</p>