Add join_natural method about arquero HOT 5 CLOSED

Thanks! You both convinced me to have Arquero throw an error when there are no shared columns. I've also updated the PR to (1) ensure proper handling of natural left, right, and full joins, and (2) suppress duplicated key column output.

The handling of full outer joins is a bit subtle. In SQL, natural full joins are not allowed. In dplyr, they are allowed but cause the "duplicate" key columns to be merged, using the value of whichever column is non-empty. I've followed the dplyr semantics here in Arquero when de-duplicating shared key columns in a full outer join.

from arquero.

Thanks for the suggestion! Rather than add a new method, I'm wondering if instead this should be the default behavior of join when given only a table as input. That sounds like it matches dplyr's behavior, as well as both your expectation and @juba's. Any thoughts?

from arquero.

I have a candidate PR for this issue here: #28

Rather than throw an error if there are no shared keys, the result is a Cartesian product (akin to cross), as this matches SQL natural join semantics.

from arquero.

I'd be happy to have this be default behavior for join, just thought this behavior might have been dictated by some standard.

That pull request works when 'on' is not specified, but it's also sensible to have this behavior if there is an on condition, as
sometimes happens with generic column names. E.g.:

  const t1 = aq.table({'state': ["MA", "NY"], 'count': [1, 4]})
  const t2 = aq.table({'state': ["MA", "NY"], 'count': [100, 400]})
  t1.join(t2, [['state'], ['state']])

could return the equivalent of

t1.join(t2, [['state'], ['state']], [aq.all(), aq.not("state")])

My fear about cross joins is that frequently when I'm being dumb, I try to join tables before properly aligning the column names and end up trying to allocate an ungodly number of rows in MySQL. dplyr supplies this message in the equivalent case: "Error: by must be supplied when x and y have no common variables. Use by = character()` to perform a cross-join."

from arquero.

I think this would be great, and I also agree with @bmschmidt caution about the potential danger of non explicitly asked for cartesian product : I already ended several times with a hanged session in R doing these sort of things, too.

from arquero.