JoinAcross

JoinAcross[{a1,a2,…},{b1,b2,…},key]

gives a list of associations obtained by joining those pairs of associations ai and bj in which the values associated with key are the same.

JoinAcross[{a1,a2,…},{b1,b2,…},{key1,key2,…}]

joins pairs of associations only when the values associated with all keys keyi are the same.

JoinAcross[{a1,a2,…},{b1,b2,…},keyakeyb]

joins pairs of associations ai, bj in which the values associated with keya in the ai are the same as those associated with keyb in the bj.

JoinAcross[{a1,a2,…},{b1,b2,…},{keya1keyb1,…}]

joins pairs of associations in which pairs of corresponding values agree.

JoinAcross[alist,blist,keyspec,"joinspec"]

uses joinspec to determine when to allow associations that contain missing elements to be generated.

Details and Options

  • JoinAcross[{a1,a2,…},{b1,b2,…},keyspec] effectively implements the analog of SQL JOIN, joining two tables with rows ai and bj across columns specified by keyspec.
  • Key specifications in JoinAcross must be wrapped in Key unless they are strings.
  • JoinAcross effectively generates a filtered version of Flatten[Outer[Join,alist,blist]].
  • By default, JoinAcross does an "inner join" in the SQL sense.
  • The following forms for "joinspec" specify different behavior for pairs of associations ai, bj:
  • "Inner"keep only those ai, bj for which values match (default)
    "Left"allow ai for which there is no matching bj
    "Right"allow bi for which there is no matching aj
    "Outer"allow both unmatched ai and bj
  • When a corresponding value is absent, Missing["Unmatched"] is used in its place.
  • The option KeyCollisionFunction can be given to specify what to do with elements of ai and bj that are not specified to be joined, but nevertheless have the same key.
  • Possible settings for KeyCollisionFunction include:
  • Nonedrop both elements
    Leftinclude only the left element (default)
    Rightinclude only the right element
    funcapply func to generate a pair of new keys

Examples

open allclose all

Basic Examples  (11)

Join two associations that have the same value for the specified key:

Join pairs of associations that have the same value for the specified key:

Join using several keys:

Join by different keys on the left and on the right:

With "Inner" join (default), combine only the matched associations:

With "Left" join, include unmatched entries from the left list:

With "Right" join, unmatched entries from the right list are included:

With "Outer" join, include all unmatched entries:

When a matching key b is not used for joining, pick the left value for this key by default:

Use KeyCollisionFunction to specify which value of the colliding key should be returned:

Generate new keys instead of a colliding key:

Omit colliding keys:

Applications  (1)

Possible Issues  (1)

Keys a and b collide, even though they are used for joining:

When the keys collide, the values from the left are taken by default:

Specify which values to take for colliding keys:

Specifically pick the right values for colliding keys:

Wolfram Research (2014), JoinAcross, Wolfram Language function, https://reference.wolfram.com/language/ref/JoinAcross.html (updated 2015).

Text

Wolfram Research (2014), JoinAcross, Wolfram Language function, https://reference.wolfram.com/language/ref/JoinAcross.html (updated 2015).

CMS

Wolfram Language. 2014. "JoinAcross." Wolfram Language & System Documentation Center. Wolfram Research. Last Modified 2015. https://reference.wolfram.com/language/ref/JoinAcross.html.

APA

Wolfram Language. (2014). JoinAcross. Wolfram Language & System Documentation Center. Retrieved from https://reference.wolfram.com/language/ref/JoinAcross.html

BibTeX

@misc{reference.wolfram_2024_joinacross, author="Wolfram Research", title="{JoinAcross}", year="2015", howpublished="\url{https://reference.wolfram.com/language/ref/JoinAcross.html}", note=[Accessed: 22-December-2024 ]}

BibLaTeX

@online{reference.wolfram_2024_joinacross, organization={Wolfram Research}, title={JoinAcross}, year={2015}, url={https://reference.wolfram.com/language/ref/JoinAcross.html}, note=[Accessed: 22-December-2024 ]}