Joining tables with full outer join and… COALESCE

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

• please always use the schema prefix when creating and referencing objects

• please always use a length when converting to varchar etc.

• stay away from ambiguous, regional date formats like mm-dd-yyyy

• consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

  SELECT m.Member_ID
    ,t.Date_Start
    ,t.Hours_Actual
    ,t.Hours_Bill
  FROM dbo.Members AS m
  LEFT OUTER JOIN dbo.Time_Entry AS t
  ON m.Member_ID = t.Member_ID
  AND t.Date_Start = '20131110';
  

  ... a lot tidier, IMHO, as long as you use sensible aliases.

Try this

select a.Date, a.Foo, b.Bar
from TableA a LEFT JOIN TableB b ON a.Date = b.Date
UNION
select b.Date, a.Foo, b.Bar
from TableB b LEFT JOIN TableA a ON a.Date = b.Date

Strictly speaking, the query above does not quite have the same semantics as the original full join, with respect to duplicates. A correct transformation of full join is to a left join union all anti-semi-join:

SELECT 
    TA.TheDate, 
    TA.Foo, 
    TB.Bar
FROM dbo.TableA AS TA
LEFT JOIN dbo.TableB AS TB 
    ON TB.TheDate = TA.TheDate
UNION ALL
SELECT 
    TB.TheDate, 
    Foo = NULL, 
    TB.Bar 
FROM dbo.TableB AS TB
WHERE NOT EXISTS 
(
    SELECT NULL
    FROM dbo.TableA AS TA 
    WHERE TA.TheDate = TB.TheDate
);

This second rewrite will also facilitate the use of an index on TheDate columns.