In Powershell, what's the best way to join two tables into one?
After 1.5 years, the cmdlet I had pasted in the original answer has undergone so many updates that it has become completely outdated. Therefore I have replaced the code and the ReadMe with a link to the latest version.
Join-Object
Main features:
- Intuitive (SQL like) syntax
- Smart property merging
- Predefined join commands for updating, merging and specific join types
- Well defined pipeline for the (left) input objects and output objects (preserves memory when correctly used)
- Performs about 40% faster than Compare-Object on large object lists
- Supports (custom) objects, data tables and dictionaries (e.g. hash tables) for input
- Smart properties and calculated property expressions
- Custom relation expressions
- Easy installation (dot-sourcing)
- Supports PowerShell for Windows (5.1) and PowerShell Core
The Join-Object cmdlet can be download from PowerShell Gallery using the command:
Install-Script -Name Join
The Join-Object cmdlet reveals the following proxy commands with their own (-JoinType
and -Property
) defaults:
InnerJoin-Object
(AliasInnerJoin
orJoin
), combines the related objectsLeftJoin-Object
(AliasLeftJoin
), combines the related objects and adds the rest of the left objectsRightJoin-Object
(AliasRightJoin
), combines the related objects and adds the rest of the right objectsFullJoin-Object
(AliasFullJoin
), combines the related objects and adds the rest of the left and right objectsCrossJoin-Object
(AliasCrossJoin
), combines each left object with each right objectUpdate-Object
(AliasUpdate
), updates the left object with the related right objectMerge-Object
(AliasMerge
), updates the left object with the related right object and adds the rest of the new (unrelated) right objects
ReadMe
The full ReadMe (and source code) is available from GitHub: https://github.com/iRon7/Join-Object
Installation
After downloading (Install-Script -Name Join
), the script can simply be invoked by dot sourcing:
. .\Join.ps1
You might also consider to convert the script to a PowerShell module by renaming it to a PowerShell module (.psm1
) file and moving it to a one of the module folders defined in $env:PSModulePath
. For more details see: How to Write a PowerShell Script Module.
Note: the Import-Module
command is required to load the proxy commands.
Answer
To answer the actual example in the question:
$reservations | LeftJoin $leases -On IP
IP MAC Name
-- --- ----
192.168.1.1 001D606839C2 Apple
192.168.1.2 00E018782BE1 Pear
192.168.1.3 0022192AF09C Banana
192.168.1.4 0013D4352A0D
Examples
More examples can be found in the related Stackoverflow questions at:
- Combining Multiple CSV Files
- Combine two CSVs - Add CSV as another Column
- CMD or Powershell command to combine (merge) corresponding lines from two files
- Can I use SQL commands (such as join) on objects in powershell, without any SQL server/database involved?
- CMD or Powershell command to combine (merge) corresponding lines from two files
- Compare Two CSVs, match the columns on 2 or more Columns, export specific columns from both csvs with powershell
- Merge two CSV files while adding new and overwriting existing entries
- Merging two CSVs and then re-ordering columns on output
- Merge two CSV files while adding new and overwriting existing entries
- Efficiently merge large object datasets having multiple matching keys
And in the Join-Object test script.
Lee Holmes wrote up a blog post on a Join-Object function that does what you want. Too bad it isn't built into PowerShell yet.