sqlpostgresqljoinnatural-join

Exclude column from natural join

I currently have the following PostgreSQL DB (picture only contains essential components): DBVis image

Basically: Each TX has some number of TXINs and some number of TXOUTs and each TXIN consists of a RING of TXOUTs (Monero transactions)

If I join the ring table with the txin table i use select * from ring natural join txin and it uses inid for the join. Same for ring and txout except it joins on outid.

Both txin and txout can also be joined with a natural join tx to get sensible outputs.

The only thing that (expectedly) does not work is:

select * from txout natural join ring natural join txin;

Because it then joins ring(inid) = txin(inid) and ring(outid) = txout(outid) (which is desired) but also txin(txid) = txout(txid) which is not desired.

I have a few ideas how to solve this problem:

  1. Use explicit joins (makes some queries quite cumbersome in my opinion), e.g. the above would be: select * from txout join ring using (outid) join txin using (inid);

  2. Rename one of the two txid columns in either txin or txout and give up on the natural join with tx

  3. Use a view of txin/txout that omits txid for these operations

Is there another (better) way? If not - which method would be best practice? Is my DB-design lacking and the whole problem could be circumvented somehow?

Solution

  • Natural joins are explicitly defined as joining on all columns with the same name. That's just how it is, it's effectively the same as (if this syntax were valid) JOIN table USING (*). There is nothing wrong with verbose SQL. It is far nicer and more maintainable to be extremely clear on your join conditions, and it'll mean if you add a column to these tables in future it won't mess with historical data. In this case I'd suggest joining using explicit syntax, as this makes it clear where the data is coming from:

    SELECT * 
FROM txout to
INNER JOIN ring r ON (to.outid = r.outid)
INNER JOIN txin ti ON (r.inid = ti.inid)

    To be clear here. Natural joins are not a maintainable solution, they're great for hacking together a view in a pinch but schemas change, and if you're using a natural join over an explicit join you will run into issues when they do.

    I'm not really sure why you've designed your DB how you have, it looks like the data has intentional overlap and could all be put in a single table of (txid, inid, outid, txhash) but perhaps I lack complete context.