0

I am trying to emulate a full outer join in mySQL by using a left join and unioning it with a right join.

SELECT * FROM auxview_candiNotes AS a
LEFT JOIN auxview_clientNotes AS b ON a.dateAdded = b.dateAdded
UNION
SELECT * FROM auxview_candiNotes AS a
RIGHT JOIN auxview_clientNotes AS b ON a.dateAdded = b.dateAdded

The reason I am doing this is because I am joining two tables and occasionally one of them will not have a value on a specific date (thus the date is not listed in the table). However the joined table should display all dates, that occur in either or both of the tables. If one of the tables does not contain a value for a specific date, it should return NULL.

However, some dates from the auxview_clientNotes table are not listed in the joined table.

Thanks in advance!

Shezan Kazi
  • 4,471
  • 3
  • 14
  • 27
  • Hi Ryan, thanks for replying. I have tried both outer joins separately and they seem to work just fine. The problem merely occurs when I use an union to put it into one table. This is where I am confused now... – Shezan Kazi Jan 03 '16 at 17:25
  • Thanks Ryan, what is the query id you are talking about? – Shezan Kazi Jan 03 '16 at 17:40
  • I built a schema here: http://sqlfiddle.com/#!9/027ee - However this one works just fine, whereas my dataset does not seem to work... – Shezan Kazi Jan 03 '16 at 18:13
  • The exact output I am looking for is a table which contains all dates and values from both tables - right now this joined table is missing some values. I will try some more debugging now, as SO asked me to avoid extended discussions in comments. – Shezan Kazi Jan 03 '16 at 18:20

1 Answers1

1

This method isn't exactly a full outer join, because the union removes duplicates. Duplicates can be generated when the underlying tables have duplicates. Often duplicate removal is considered a "good" think, but the method is not exactly equivalent to a full outer join.

I prefer a left join approach:

select candi.*, client.*
from (select dateAdded from auxview_candiNotes union -- intentionally not `union all`
      select dateAdded from auxview_clientNotes
     ) d left join
     auxview_candiNotes candi
     on candi.dateAdded = d.dateAdded left join
     auxview_clientNotes client
     on client.dateAdded = d.dateAdded;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Good evening Gordon, Thanks a lot for taking the time! Unfortunately with your query there are still some missing values, if the date exists in the "right" table (i.e. clientNotes) but not in the left table. – Shezan Kazi Jan 03 '16 at 16:55
  • @5h3z4n . . . Edit your question and provide sample data. This should return all dates and the associated records. – Gordon Linoff Jan 04 '16 at 03:09