Flow/merge node - left & right join

The idea is:

right now the merge node has two modes that can be compared to SQL join:

  • merge by key - behaves like inner join
  • multiplex - behaves like outer join

Please add left join, which retains items from the first input when there’s no matching item in the second input

My use case:

My items have a field, say company_name, that I need to fill based on another field which might be empty, say, company_id.
I take all companies and try to merge it using company_id as the matching field.
If company_id is missing or otherwise not matching, I’d like to keep that item in the output.

I think it would be beneficial to add this because:

Left join is quite common in SQL and merge node looks like it’s supposed be something similar

Hi @mattesilver,

Merge by key should be a left join as far as I have seen.

EDIT:
I was doubting myself, so I checked it to be sure. I used an example from node page. and modified it so it would show the left join.

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "items[0].json = {\n\"data\": [\n{\n\"pointer\": \"12345\",\n\"panel\": \"234234\",\n\"subject\": \"Blah Blah\",\n\"note\": \"\",\n\"interviewers\": [\n{\n\"id\": \"111222333\",\n\"name\": \"Bobby Johnson\",\n\"email\": \"[email protected]\"\n}\n],\n\"timezone\": \"America/Los_Angeles\",\n},\n{\n\"pointer\": \"98754\",\n\"panel\": \"3243234\",\n\"subject\": \"Yadda Yadda\",\n\"note\": \"\",\n\"interviewers\": [\n{\n\"id\": \"444555666\",\n\"name\": \"Billy Johnson\",\n\"email\": \"[email protected]\"\n}\n],\n\"timezone\": \"America/Los_Angeles\",\n},\n],\n\"hasNext\": false\n};\nreturn items;\n"
      },
      "name": "Data 1",
      "type": "n8n-nodes-base.function",
      "position": [
        607,
        335
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "functionCode": "items[0].json = [\n{\n\"name\": \"test\",\n\"fields\": {\n\"FirstName\": \"Bobby\",\n\"LastName\": \"Johnson\",\n\"JobTitleDescription\": \"Recruiter\",\n\"HomeDepartmentDescription\": \"Recruiting Team\",\n\"Photo\": [\n{\n\"x\": \"attPuc6gAIHUOHjsY\",\n\"url\": \"http://urlto.com/BobbyPhoto.jpg\",\n\"filename\": \"photo.jpg\",\n\"size\": 28956,\n\"type\": \"image/jpeg\"\n}\n],\n\"eid\": \"22222111222333\"\n},\n\"createdTime\": \"2019-09-23T04:06:48.000Z\"\n},\n{\n\"name\": \"test2\",\n\"fields\": {\n\"FirstName\": \"Billy\",\n\"LastName\": \"Johnson\",\n\"JobTitleDescription\": \"CEO\",\n\"HomeDepartmentDescription\": \"Boss Team\",\n\"Photo\": [\n{\n\"x\": \"attPuc6gAIHUOHjsY\",\n\"url\": \"http://urlto.com/BillyPhoto.jpg\",\n\"filename\": \"photo.jpg\",\n\"size\": 28956,\n\"type\": \"image/jpeg\"\n}\n],\n\"eid\": \"444555666\"\n},\n\"createdTime\": \"2019-09-23T04:06:48.000Z\"\n}\n,\n{\n\"name\": \"test3\",\n\"fields\": {\n\"FirstName\": \"Susan\",\n\"LastName\": \"Smith\",\n\"JobTitleDescription\": \"CFO\",\n\"HomeDepartmentDescription\": \"Boss Team\",\n\"Photo\": [\n{\n\"x\": \"attPuc6gAIHUOHjsY\",\n\"url\": \"http://urlto.com/SusanPhoto.jpg\",\n\"filename\": \"photo.jpg\",\n\"size\": 28956,\n\"type\": \"image/jpeg\"\n}\n],\n\"eid\": \"777888999\"\n},\n\"createdTime\": \"2019-09-23T04:06:48.000Z\"\n}\n];\nreturn items;"
      },
      "name": "Data 2",
      "type": "n8n-nodes-base.function",
      "position": [
        607,
        535
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "functionCode": "const newItems = [];\n\nfor (const item of items[0].json.data) {\n  newItems.push({ json: item });\n}\n\nreturn newItems;"
      },
      "name": "Convert Data 1",
      "type": "n8n-nodes-base.function",
      "position": [
        760,
        340
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "functionCode": "const newItems = [];\n\nfor (const item of items[0].json) {\n  newItems.push({ json: item });\n}\n\nreturn newItems;"
      },
      "name": "Convert Data 2",
      "type": "n8n-nodes-base.function",
      "position": [
        757,
        535
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "mode": "mergeByKey",
        "propertyName1": "interviewers[0].id",
        "propertyName2": "fields.eid"
      },
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "position": [
        995,
        415
      ],
      "typeVersion": 1
    }
  ],
  "connections": {
    "Data 1": {
      "main": [
        [
          {
            "node": "Convert Data 1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Data 2": {
      "main": [
        [
          {
            "node": "Convert Data 2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Convert Data 1": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Convert Data 2": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    }
  }
}