title: Full Outer Join three tables in MySQL
body: <p>I have following tables and desired output. I tried like full join based on value and group by subject but its still not showing the desired output <a href="https://i.stack.imgur.com/QirOO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QirOO.png" alt=""></a></p>
  
  <p>Any help or guidance will be much appreciated. Below is the MySql query:</p>
  
  <pre><code>select t1.*,t2.value from Table1 as t1 left join Table2 as t2
  on RIGHT(t2.value,1) =RIGHT(t1.value,1) 
  
  union all 
  select t1.*,t2.value from Table1 as t1 right join Table2 as t2
  on RIGHT(t2.value,1) =RIGHT(t1.value,1)
  </code></pre>
  
  <p>DDL: </p>
  
  <pre><code>CREATE TABLE Table1 (`subject` varchar(30), `value` varchar(30));
  CREATE TABLE Table2 (`subject` varchar(30), `value` varchar(30));
  CREATE TABLE Table3 (`subject` varchar(30), `value` varchar(30));
  
  INSERT INTO Table1
  (`subject`, `value`)
  VALUES
  ('subject1', 'ValueA1'),
  ('subject1', 'ValueA2'),
  ('subject1', 'ValueA3'),
  ('subject2', 'ValueA4'),
  ('subject2', 'ValueA5'),
  ('subject3', 'ValueA6'),
  ('subject3', 'ValueA7');
  
  INSERT INTO Table2
  (`subject`, `value`)
  VALUES
  ('subject1', 'ValueB1'),
  ('subject1', 'ValueB2'),
  ('subject2', 'ValueB3'),
  ('subject1', 'ValueB4'),
  ('subject2', 'ValueB5'),
  ('subject3', 'ValueB6');
  
  INSERT INTO Table3
  (`subject`, `value`)
  VALUES
  ('subject1', 'ValueC1'),
  ('subject2', 'ValueC2'),
  ('subject2', 'ValueC3'),
  ('subject2', 'ValueC4'),
  ('subject2', 'ValueC5'),
  ('subject3', 'ValueC6');
  </code></pre>