{"action":"created","issue":{"url":"https://api.github.com/repos/datajoint/datajoint-python/issues/263","repository_url":"https://api.github.com/repos/datajoint/datajoint-python","labels_url":"https://api.github.com/repos/datajoint/datajoint-python/issues/263/labels{/name}","comments_url":"https://api.github.com/repos/datajoint/datajoint-python/issues/263/comments","events_url":"https://api.github.com/repos/datajoint/datajoint-python/issues/263/events","html_url":"https://github.com/datajoint/datajoint-python/issues/263","id":191047954,"number":263,"title":"Outer joins","user":{"login":"dimitri-yatsenko","id":1096169,"avatar_url":"https://avatars.githubusercontent.com/u/1096169?v=3","gravatar_id":"","url":"https://api.github.com/users/dimitri-yatsenko","html_url":"https://github.com/dimitri-yatsenko","followers_url":"https://api.github.com/users/dimitri-yatsenko/followers","following_url":"https://api.github.com/users/dimitri-yatsenko/following{/other_user}","gists_url":"https://api.github.com/users/dimitri-yatsenko/gists{/gist_id}","starred_url":"https://api.github.com/users/dimitri-yatsenko/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dimitri-yatsenko/subscriptions","organizations_url":"https://api.github.com/users/dimitri-yatsenko/orgs","repos_url":"https://api.github.com/users/dimitri-yatsenko/repos","events_url":"https://api.github.com/users/dimitri-yatsenko/events{/privacy}","received_events_url":"https://api.github.com/users/dimitri-yatsenko/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-11-22T16:03:49Z","updated_at":"2016-11-22T19:39:47Z","closed_at":null,"body":"Currently, DataJoint only offers inner joins and the need for outer joins has not really come up.  But supply creates demand, so let's offer support for outer joins. \r\n\r\nOuter joins are already implicitly performed in `aggr`, where datajoint provides the `keep_all_rows` option, which internally, replaces the join within the aggregation with an outer join.\r\n\r\nI propose to support left, right, and full outer joins, including left join in aggregation.  Thankfully, DataJoint syntax makes it fairly intuitive. \r\n\r\nI propose the following syntax:\r\n\r\n### Inner join remains unchanged\r\n\r\n```python\r\ninner_join = a * b\r\n```\r\n\r\n### Left and right joins\r\nLeft joins are made by transforming the right argument into its outer version\r\n\r\n```python\r\nleft_join = a * b.outer\r\n```\r\nThis means that all rows of `a` are retained.  For those rows with no matching rows in `b`, the missing values in `b` are replaced with `NULL` (`None` in Python)\r\n\r\nSimilarly, in right joins, the left argument is transformed into its outer version.\r\n\r\n```python\r\nright_join = a.outer * b\r\n```\r\n\r\n### Full outer joins\r\nIn full outer joins, both arguments are transformed into their outer versions.\r\n```python\r\nouter_join = a.outer * b.outer\r\n```\r\nSince MySQL does not support full outer joins, we will probably disallow them too.\r\n\r\n### Aggregation\r\nThe aggregated relation can be turned into its outer version to ensure that all rows of the aggregating relation are kept.\r\n\r\n```python\r\naggr = a.aggr(b.outer, n='count(b_id)')   \r\n```\r\nreplaces the current syntax of \r\n```python\r\naggr = a.aggr(b, n='count(b_id)', keep_all_rows=True)\r\n```\r\nWhen `dj.U` is the aggregating relation, then the aggregated relation cannot be outer.  The following will raise an exception: \r\n```python\r\naggr = dj.U('color').aggr(b.outer, n='count(b_id)')\r\n```\r\n`dj.U` itself cannot be made outer.\r\n\r\nAlthough SQL allows aggregations by attributes with NULLs, I think aggregation by an outer relation should be forbidden:\r\n```python\r\na.outer.aggr(b, n='count(*)')    # error: Outer relations cannot aggregate.\r\n``` \r\n\r\n### Restriction\r\n```python\r\na & b.outer     # ignores the restriction \r\n```\r\nignores the restriction.  This should probably just raise the exception \"Restrictions by outer relations are not allowed.\"\r\n\r\n### Properties of the result\r\nThe primary key of all joins is the union of the primary key attributes of the arguments.  However, left, right, and full outer joins may contain nulls.\r\n\r\nThe only difference is that now the primary key may contain some `None` values.  When treated as a regular value, the uniqueness of primary key values in the result is still preserved.\r\n\r\nThe result of an outer join may contain NULLs in its primary key attributes.  Outright, the result will not be flagged as outer itself. The rows with NULLs will be ignored in any inner joins or in restrictions.  The only time they will matter is in joins and aggregations where the other relation is outer itself.\r\n\r\nThe outer flag will be preserved in restriction and projection.  Thus `(a.outer & condition)` is equivalent to `(a & condition).outer` and `a.proj().outer` is equivalent to `a.outer.proj()`."},"comment":{"url":"https://api.github.com/repos/datajoint/datajoint-python/issues/comments/262343409","html_url":"https://github.com/datajoint/datajoint-python/issues/263#issuecomment-262343409","issue_url":"https://api.github.com/repos/datajoint/datajoint-python/issues/263","id":262343409,"user":{"login":"fabiansinz","id":7535854,"avatar_url":"https://avatars.githubusercontent.com/u/7535854?v=3","gravatar_id":"","url":"https://api.github.com/users/fabiansinz","html_url":"https://github.com/fabiansinz","followers_url":"https://api.github.com/users/fabiansinz/followers","following_url":"https://api.github.com/users/fabiansinz/following{/other_user}","gists_url":"https://api.github.com/users/fabiansinz/gists{/gist_id}","starred_url":"https://api.github.com/users/fabiansinz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fabiansinz/subscriptions","organizations_url":"https://api.github.com/users/fabiansinz/orgs","repos_url":"https://api.github.com/users/fabiansinz/repos","events_url":"https://api.github.com/users/fabiansinz/events{/privacy}","received_events_url":"https://api.github.com/users/fabiansinz/received_events","type":"User","site_admin":false},"created_at":"2016-11-22T19:39:47Z","updated_at":"2016-11-22T19:39:47Z","body":"I'd like unions."}}
{"actor":{"display_login":"fabiansinz"}}