Include NULL row on join

How can I select all rows from table_A and then join on results from other tables, but include a NULL row to show aggregate values for those other tables that don't have a table_A_id?

WITH RECURSIVE transactions_with_children AS (
  SELECT
    table_A_id,
    other_stuff,
    1 AS depth
  FROM transactions
  WHERE transactions.parent_transaction_id IS NULL
  UNION ALL
  SELECT
    table_A_id,
    other_stuff,
    depth + 1 AS depth
  FROM transactions children
    INNER JOIN transactions_with_children parents ON children.parent_transaction_id = parents.id
)

SELECT 
  table_A.id,
  view_B.aggregate_col1, view_B.aggregate_col2,
  view_C.aggregate_col1, view_C.aggregate_col2

FROM table_A
  -- Limit table_A to only records with data from transactions_with_children, but I also need to include
  -- a null row for all transactions_with_children that don't have a table_A_id
  INNER JOIN transactions_with_children on transactions_with_children.table_A_id = table_A.id
  LEFT JOIN (SELECT
               t.table_A_id,
               aggregate_col1, aggregate_col2
             FROM transactions_with_children t
             GROUP BY t.table_A_id
            ) view_B
    ON table_A.id = view_B.table_A_id
  LEFT JOIN (SELECT
               t.table_A_id,
               aggregate_col1, aggregate_col2
             FROM transactions_with_children t
             GROUP BY t.table_A_id
            ) view_C
    ON table_A.id = view_C.table_A_id

The recursive WITH tables are all scoped records from a large table. Each of the LEFT JOINs that i'm doing using this table just aggregate data from it. SUM, COUNT etc. I then join on the common table_A_id that each of the records from the WITH may or may not have.

So the problem with doing a RIGHT JOIN on transactions_with_children (I think) is that I would get tons of duplicates from transactions_with_children table. I guess I could maybe create an RIGHT JOIN sub select where I am pulling records from transactions_with_children and gr

Use RIGHT JOIN instead of INNER JOIN right after your comment in the code:

Limit table_A to only records with data from
transactions_with_children, but I also need to include a null row for
all transactions_with_children that don't have a table_A_id

RIGHT [OUTER] JOIN keeps all rows from tables to the right of the join and adds NULL values for rows from the tables left of the join where none match the join condition. Your text is a bit ambiguous, you might want LEFT JOIN instead, which is the same with reversed sides.

Basics in the manual here.

StackExchange Database Administrators Q#174627, answer score: 9