Full Outer Join / COALESCE

Question

I would like to get some experts advice

I have 2 tables Table1 and Table2

Table 1

ID  DATE         SALEPRICE

1   05/12/17     $10
6   04/12/17     $11
3   03/12/17     $12
4   02/12/17     $13

Table 2

ID  DATE         REGULARPRICE

1   07/12/17     $20
2   08/12/17     $21
3   09/12/17     $22
4   10/12/17     $23

Result Table

ID  SALEDATE         SALEPRICE REGULARPRICE   REGULARPRICEDATE

1   05/12/17          $10       $20           07/12/17
6   04/12/17          $11         
3   03/12/17          $12       $22           09/12/17
4   02/12/17          $13       $23           10/12/17
2                               $21           08/12/17

I need entries from both the tables ..If i have a match of id from both the tables then i want to get the record in a single row ..

I thought of using the Union but the results are not correct - Below are my union results

1   05/12/17          $10       null          null
1   null              null      $20           07/12/17
6   04/12/17          $11       null          null  
3   null              null      $22           09/12/17
3   03/12/17          $12       null          null  
4   02/12/17          $13       null              null
4   null              null      $23           10/12/17

Do i have to use Full Outer Join and COALESCE to get the desired results

Thanks in advance

Depends on RDBMS. mySQL doesn't support a full outer but a two outer and a union all would do it. Not sure why a coalesce is needed however. or you could continue with what you have and simply max each column and group by the ID (less elegant imo) — xQbert
Which SQL? It's useless if someone give you solution to the wrong SQL. — Eric

Ryan Gadsdon Ryan Gadsdon · Accepted Answer · 2017-09-14T16:23:46

You could potentially use both. Full outer join to return all of your records regardless of matches and then COALESCE to merge the same columns into one. Using COALESCE is only useful if the data in both columns is exactly the same. Thats if you want to merge the columns. If not, then just use full outer join.

Full Outer Join / COALESCE

1 Answers