René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Unions and Outer Joins","articleBody":"So far DataJoint has not offered the union operator and the outer join operators.  These two operators are closely related.  I propose to implement outer join/union as the `+` operator.\r\n\r\n## Syntax\r\n```python\r\na + b    # full outer join of relations `a` and `b` \r\n```\r\nIncidentally, outer join is equivalent to union when the two arguments have the same sets of attributes.  \r\n\r\nNote that MySQL does not implement full outer joins, so internally it will be implemented as a left join followed by a union with the right argument.\r\n\r\n## Rules \r\nThe full outer join follows the same rules as the join operator `a*b`:\r\n1.  The primary key of the expression `a+b` is the union of the primary key attributes of `a` and `b`.\r\n1.   The matching attributes in `a` and `b`, i.e. those sharing the same names in both relations must be of compatible datatypes and must belong to the primary key of either `a` or `b`.  An exception will be raised if a non-key attribute with the same name is present in both relations. \r\n1.  The order of attributes in the arguments does not matter and is not guaranteed in the result.\r\n\r\n## Equivalence to OUTER JOIN\r\n`a+b` is equivalent to the natural outer join operator in relational algebra.  \r\n\r\n## Equivalence to SQL's UNION\r\n`a+b` is equivalent to relational union if `a` and `b` have the same sets of attributes. \r\n\r\n## Implementation of LEFT and RIGHT OUTER JOINs\r\nThe natural left join (`a NATURAL LEFT JOIN b`) can now be expressed as\r\n```python\r\na * b + a\r\n```\r\nor, equivalently, as\r\n```python\r\n(a + b) \u0026 a\r\n```\r\nor, equivalently, as\r\n```python\r\na + (b \u0026 a) \r\n```\r\nor, equivalently\r\n```python\r\n(a - b) + b\r\n``` \r\n\r\nIt is difficult to make DataJoint recognize all these patterns and to convert them into efficient SQL, so let's introduce the special syntax indicating left or right joins:\r\n\r\n```python\r\na * ~b      # left outer join  (all rows of `a` are kept).\r\n~a * b      # right outer join (all rows of `b` are kept). \r\n~a * ~b  # outer join (equivalent to `a+b`)\r\n```\r\n\r\nThe outer flag `~` survives projections and restrictions but not other operators:\r\n```python\r\n~(a \u0026 cond) * b    # equivalent to   (~a \u0026 cond) * b\r\n~a.proj() * b    # equivalent to   (~a).proj() * b\r\n```\r\n\r\n## Examples\r\n### Example 1:  Simple union\r\nLet relation `a` with primary key (i, k) equal \r\n\r\n| *i \t| *k \t| \r\n|:-:\t|:-:\t|\r\n|   1\t|  1 \t|\r\n|   1\t|  2\t|\r\n|   2\t|  1 \t|\r\n\r\nLet relation `b` with primary key (k) equal \r\n\r\n| *k \t|  i \t| \r\n|:-:\t|:-:\t|\r\n|   1\t|   1\t|\r\n|   2\t|   1\t|\r\n|   2\t|  3\t|\r\n\r\nThen `a+b` will be\r\n\r\n| *i \t| *k \t| \r\n|:-:\t|:-:\t|\r\n|   1\t|  1 \t|\r\n|   1\t|  2\t|\r\n|   2\t|  1 \t|\r\n|   3\t|  2 \t|\r\n\r\n### Example 2: No matching attributes:  outer join = inner join\r\n\r\nLet relation `a` with primary key (i) equal \r\n\r\n| *i \t| m | \r\n|:-:\t|:-: |\r\n|   1\t|  6  |\r\n|   2\t|  6 \t|\r\n\r\nLet relation `b` with primary key (i, k) equal \r\n\r\n|  *k \t| n | \r\n|:-:\t|:-: |\r\n|   1\t|   8 |\r\n|   2\t|   8 |\r\n\r\nThen `a+b` will be\r\n\r\n| *i \t| *k \t| m  | n  |\r\n|:-:\t|:-:\t|:-:  |:-:  |\r\n|   1\t|   1\t| 6 | 8 |\r\n|   1\t|   2\t| 6 | 8 |\r\n|   2\t|   1\t| 6 | 8 |\r\n|   2 |    2  | 6 | 8 |\r\n\r\n\r\n### Example 3:  Simple outer join\r\n\r\nLet relation `a` with primary key (i) equal \r\n\r\n| *i \t| m | \r\n|:-:\t|:-: |\r\n|   1\t|  6  |\r\n|   2\t|  6 \t|\r\n|   3\t|  6 \t|\r\n\r\nLet relation `b` with primary key (i, k) equal \r\n\r\n| *i \t|  *k \t| n | \r\n|:-:\t|:-:\t|:-: |\r\n|   1\t|   1\t| 8 |\r\n|   1\t|   2\t| 8 |\r\n|   2\t|  3\t| 8 |\r\n|   4 |   1  | 8 |\r\n\r\nThen `a+b` will be\r\n\r\n| *i \t| *k \t| m  | n  |\r\n|:-:\t|:-:\t|:-:  |:-:  |\r\n|   1\t|   1\t| 6 | 8 |\r\n|   1\t|   2\t| 6 | 8 |\r\n|   2\t|  3\t| 6 | 8 |\r\n|   4 |   1  | NULL | 8 |\r\n|   3  |  NULL | 6 | NULL\r\n\r\nNote `a+b` may produce NULLs in the primary key. ","author":{"url":"https://github.com/dimitri-yatsenko","@type":"Person","name":"dimitri-yatsenko"},"datePublished":"2016-11-22T21:55:14.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/264/datajoint-python/issues/264"}