Full Outer Join on two columns is omitting rows

Question

Some background, I am making a table in Postgres 9.5 that counts the number of actions performed by a user and grouping these actions by month using date_trunc(). The counts for each individual action are divided into separate tables, following this format:

Feedback table:                   
id  |  month  |  feedback_counted 
----+---------+-------------------
 1  |    2    |         3         
 1  |    3    |        10         
 1  |    4    |         7         
 1  |    5    |         2         

Comments table:
id  |  month  |  comments_counted
----+---------+-------------------
 1  |    4    |         12
 1  |    5    |          4
 1  |    6    |         57
 1  |    7    |         12

Ideally, I would like to do a FULL OUTER JOIN of these tables ON the "id" and "month" columns at the same time and produce this query:

Combined table:
id  |  month  |  feedback_counted  |  comments_counted
----+---------+--------------------+-------------------
 1  |    2    |         3          |         
 1  |    3    |        10          |        
 1  |    4    |         7          |        12
 1  |    5    |         2          |         4
 1  |    6    |                    |        57
 1  |    7    |                    |        12

However, my current query does not capture the feedback dates, displaying it like such:

Rollup table:
id  |  month  |  feedback_counted  |  comments_counted
----+---------+--------------------+-------------------    
    |         |                    |       
    |         |                    |       
 1  |    4    |         7          |        12
 1  |    5    |         2          |         4
 1  |    6    |                    |        57
 1  |    7    |                    |        12

This is my current statement, note that it uses date_trunc in place of month. I add the action counts later, the main issue is somewhere here.

CREATE TABLE rollup_table AS 
SELECT c.id, c.date_trunc
    FROM comments_counted c FULL OUTER JOIN feedback_counted f 
    ON c.id = f.id AND c.date_trunc = f.date_trunc
GROUP BY c.id, c.date_trunc, f.id, f.date_trunc;

I'm a bit of a novice with SQL and am not sure how to fix this, any help would be appreciated.

Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Jul 09 '20 at 18:36

klin · Accepted Answer · 2021-11-04T11:07:34.547

Replace ON c.id = f.id AND c.month = f.month with USING(id, month).

SELECT id, month, feedback_counted, comments_counted
FROM comments c 
FULL OUTER JOIN feedback f 
USING(id, month);

 id | month | feedback_counted | comments_counted 
----+-------+------------------+------------------
  1 |     2 |                3 |                 
  1 |     3 |               10 |                 
  1 |     4 |                7 |               12
  1 |     5 |                2 |                4
  1 |     6 |                  |               57
  1 |     7 |                  |               12
(6 rows)

Test it in db<>fiddle.

score 0 · Answer 2 · answered Jul 09 '20 at 17:52

USING() basically is the same as ON, just that if the 2 tables share the same column names, you can use USING() instead of ON to save some typing effort. That being said, using USING() won't work. In Postgresql (not sure about other sql versions), you still need to specify c.id, and c.month, even with USING(). And as long as you specify the columns, Postgresql will only pull the rows where the values of these columns exist. That's why you will have missing rows under the full outer join.

Here is a way that at least works for me.

SELECT COALESCE(c.id, f.id) AS id, 
       COALESCE(c.month, f.month) AS month, 
       feedback_counted, 
       comments_counted
FROM comments c 
FULL OUTER JOIN feedback f 
ON c.id = f.id AND c.month = f.month;

Full Outer Join on two columns is omitting rows

2 Answers2