What you are trying to accomplish is usually done with a UNION in SQL, not JOIN. Roughly:

SELECT account_id, amount as inflow, NULL as outflow FROM transaction WHERE type = 'inflow' AND account_id = ACCOUNT_ID

1, 1000, NULL
1, 1000, NULL

SELECT account_id, NULL as inflow, amount as outflow FROM transaction WHERE type = 'outflow' AND account_id = ACCOUNT_ID

1, NULL,  500
1, NULL, 1500

(
  SELECT account_id, amount as inflow, NULL as outflow FROM transaction WHERE type = 'inflow' AND account_id = ACCOUNT_ID
) UNION ALL (
  SELECT account_id, NULL as inflow, amount as outflow FROM transaction WHERE type = 'outflow' AND account_id = ACCOUNT_ID
)

1, 1000, NULL
1, 1000, NULL
1, NULL,  500
1, NULL, 1500

SELECT account_id, SUM(inflow), SUM(outflow) FROM (
  SELECT account_id, amount as inflow, NULL as outflow FROM transaction WHERE type = 'inflow' AND account_id = ACCOUNT_ID
) UNION ALL (
  SELECT account_id, NULL as inflow, amount as outflow FROM transaction WHERE type = 'outflow' AND account_id = ACCOUNT_ID
)
 GROUP BY account_id

1, 2000, 2000

Ecto doesn’t support unions so the easiest alternative is two separate queries - one for inflow another for outflow.

Sort of. If you’re doing a preload with a join, the rows come out of the database with duplicates, and then Ecto de-duplicates them. There are plenty of situations where Ecto doesn’t de-duplicate stuff, ie:

iex(2)> length Repo.all from u in User
401
iex(3)> length Repo.all from u in User, join: m in assoc(u, :memberships)
674

Will return multiple copies of the same user if they have have more than one membership. This is a good thing really, it provides a nice SQL like experience.

In a way though the OP’s question is a whole other scenario. The duplication happens at the SQL level, and so does the aggregation, so there isn’t even an opportunity for Ecto to deduplicate even if it wanted to.

FWIW in Ecto master there’s support for the filter expressions. Using them, this could be achieved with a simpler query:

{account, inflow, outflow} =
     Repo.one(from a in App.Account,
       left_join: t in App.Transaction,
       on: t.account_id == a.id and t.user_id == ^current_user.id and not t.deleted,
       where: a.id == ^id,
       group_by: a.id,
       select: {a, filter(sum(t.amount), t.type == "inflow"), filter(sum(t.amount), t.type == "outflow")})