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,spec,"jspec"]
uses jspec to determine when to allow associations that contain missing elements to be generated.

Details and OptionsDetails and Options

  • JoinAcross[{a1,a2,},{b1,b2,},spec] effectively implements the analog of SQL JOIN, joining two tables with rows ai and bj across columns specified by spec.
  • 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 "jspec" 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

ExamplesExamplesopen allclose all

Basic Examples  (11)Basic Examples  (11)

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

In[1]:=
Click for copyable input
Out[1]=

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

In[1]:=
Click for copyable input
Out[1]=

Join using several keys:

In[1]:=
Click for copyable input
Out[1]=

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

In[1]:=
Click for copyable input
Out[1]=

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

In[1]:=
Click for copyable input
Out[1]=

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

In[1]:=
Click for copyable input
Out[1]=

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

In[1]:=
Click for copyable input
Out[1]=

With "Outer" join, include all unmatched entries:

In[1]:=
Click for copyable input
Out[1]=

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

In[1]:=
Click for copyable input
Out[1]=

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

In[2]:=
Click for copyable input
Out[2]=

Generate new keys instead of a colliding key:

In[1]:=
Click for copyable input
Out[1]=

Omit colliding keys:

In[1]:=
Click for copyable input
Out[1]=
Introduced in 2014
(10.0)
| Updated in 2015
(10.3)
Translate this page: