Skip to main content
Version: Python

Choose a join method

This guide discusses the set of join methods in Deephaven and their differences. There are several methods to choose from, and while the basic syntax is the same, this article will show you how to choose which join is right for your use case.

Frequently, related data is stored in two different tables, and you'd like to create a single table containing data from both data sources. Join methods allow you to do this. Each join method produces a different result, so it is important to know which produces your desired output. If more than two tables need to be joined, then the process can be repeated.

The following flowchart will help you choose which join is right for your use case.

eyJ2ZXJzaW9uIjoiMSIsImVuY29kaW5nIjoiYnN0cmluZyIsImNvbXByZXNzZWQiOnRydWUsImVuY29kZWQiOiJ4nO1dW1PbSFx1MDAxNn6fX+FiX8faPn3vfdniToBwXHJcdTAwMTiz2aKELV+CkcCWwWZq/vueNsSSrItcdTAwMWSwXHUwMDEzb8rMXHUwMDE0MZLcUnef7zvXbv31R6m0XHUwMDE2XHUwMDBlXHUwMDFmvLV/lda8Qc3ttOtd93ntT3v8yev22oGPp+jo717Q79ZGV7bC8KH3r3/+M/qGU1x1MDAwYu5fv+V1vHvPXHUwMDBme3jdf/DvUumv0W88067b77Lh491Z8+pksHlxdOa77WOtq6ejr44u+v4w9bZ7XHUwMDFm+PXoxFx1MDAwMI9qrbgjXHUwMDE010wxJrlS47NDPFtcdTAwMDZGtcNcdTAwMDSjTINcdTAwMDYuqVx1MDAxZZ9/btfDXHUwMDE2XoMnx8daXrvZXG7xIDNsfND1m1x1MDAxZPtcdTAwMDBkfKRcdTAwMTd2gztvM+hcdTAwMDRd+2D/aDRcdTAwMWHRU926tbtmN+j79ehcdTAwMDIwtM51dE2j3emch8PO69C5tVa/661NNF95ez4+cXz8vV6AXHUwMDAzXHUwMDFkfVx1MDAwYm/ZbPler/d9dkZHg1x1MDAwN7fWXHUwMDBl7VhcdTAwMDCJOmCf7+FTfTQj/51sv+V2XHUwMDFm3tpZXHUwMDFi9SP2ZJ5nZ0xwXHUwMDBlgshYg5FgSFxyk0ePXHUwMDAyfyQklFDKpVEk6lK7t4XSXHUwMDExjlptuJ2eXHUwMDE3XHKjvfP2pOTEpScmQc/fzvy93es6XHUwMDE5qMbW833/snxcdTAwMWSE48dOSFHoXHLCtfGJv//MbjZx9Z+z3nBqs69fvlx1MDAxZqohhGeyRdrlvfPri8Hx3pXKflq321xynmPtvn2K5qz/UHdfh1x1MDAwZqRgRHHCKCXRXHUwMDE0dNr+XHUwMDFknvT7nU50LKjdRSP+R+yBJ4BZPKzZg/RcdTAwMWSVplxilWDMNFTSXGZUXHUwMDAyXHUwMDEzK1TmopJcdTAwMTJcdTAwMDacaSl5XHUwMDA2LFx1MDAxNVF5sOTaXHUwMDAwwfF+XHUwMDBmKlx1MDAxM3L1cVmMRMuKXHUwMDE09nQrKLmdTqlcdTAwMTZ0+vd+76vfcp+8kjdwa+FX/95ccmstr/fv2Fx1MDAwNFx1MDAwNn543n6xnWIycXTHvW93RuOduMV6p920o7NWw/543bX4XHUwMDEwhW3UZONcdTAwMGLu2/V6x4vLU8/D/thbXHUwMDAxjVx1MDAxYa3hvVxcPNz9NItmXHUwMDBiuu1m23c7X36kt1x1MDAwNXi9kp/4XHUwMDEz2Tu7qFxcPFx1MDAxZFx1MDAxZIj+5aDyXFxL4/WVVFx1MDAxMoA1yOeOMEJcdTAwMWJCjFx1MDAwMMWTgDWcO0SAUEzhSVA0hVfJtaOl0JxQSVx1MDAxMPAmjV6UMFx1MDAwNy/gXHUwMDAw2FxiXHUwMDE4tdKw+Vg2kkukT62zoMxyoVxmklx1MDAxMVTNhi5cdTAwMTGUXHUwMDFmgvakXHUwMDA2jz6VojlcdTAwMWb9Mf783z8zry5cdTAwMGLUK1xcMFx1MDAxNEipKVxiTeNfR61cdTAwMDKcUpBKKoXPK2doXHUwMDBlXHUwMDFjw1xy5VpoRlxyZ/HmXHUwMDAwiCMl6jJFuVwiQimY2l4uXGbsT1xuXHUwMDAwUXMpvd5xe+FmcH/fXHUwMDBlcVx1MDAxYU7sXHUwMDEwTlx1MDAwZXcvdLvhRtuvt/3m5DnPr0dnXCJcdTAwMGJcdTAwMDSpsNa3U1EmXHUwMDBlSGKoNlx1MDAxMjRcdTAwMTOohbmInnSt6T5YSDvUUEE1XHUwMDAxJFx1MDAwNWJ47II3i/6V5Kq6xq/7uyeNb7XKsH9TPWpcdTAwMWTSy+82y9/J51231NPy3HrGXHUwMDEzx8/FXHKfbK4rNqKmcJ10XGZwpTmTRE9SXHUwMDFkXGLHoIChdFx1MDAxOUGNyaA6plEsmFx1MDAwMlx1MDAwM1xiRVxuXHUwMDExRiOqXHUwMDAz6lx1MDAxOKVcdTAwMTi2oDXF8V3ZLflcXFx1MDAwN1pcdTAwMTJEmZFZZKdZyslcdTAwMTiTnVx1MDAwMmqI1lwiUvW/XHUwMDE520nlIPAkXHUwMDEzRlGhXGJLkJ1cdTAwMTJcdTAwMGVcdTAwMDNKrVx1MDAxNU2kYVJNa01I7XAucMSIllx1MDAxYf9NtEZcdTAwMWQgeCNcdTAwMDS84qD1VKrLRYH9Scv/PKkug9AsUeO8cU5RbVK8cYx43+iMjsaLXHUwMDAxMYZpwTWNcX2S0LKttlx1MDAxNKElSDb9vPOjututwXa4v9c6qlUrO+Lz3Wn1eofPXHUwMDE4XHUwMDFmQWNOxDwxXHUwMDE5YXnEdlxu2DRHjEfwisIjXCIyQVaEllwiNFx1MDAwNFx1MDAxMaFcdTAwMTSNgCzrLUZXXHUwMDEzhCY1XG4vZ+o9fFZcdTAwMThcdTAwMWXpn9X0zdFm93T7huxcdTAwMDePL8NP3Tb9SHjktdly9fC089BSXHUwMDFi5euH5vXmXmN7vdmfLY5R2O5TZ1A9Pn5Ux1tnnatLqPVcdTAwMGV2yy9cdTAwMWZqd1o4J3uAomZTnLXQsEvxdFx1MDAxNYRdkG5JXHUwMDAx2CWhU2OhXHUwMDE5YF9FXVxuwa5cZqpmXHUwMDE1XHUwMDBmasbAXHUwMDFlm4JJ68VcdTAwMWHWXHUwMDFhp0m/XHUwMDAz7T8h7DJcZvqlZ9dcdTAwMGa/+mHLK/me2/V6Ycl78uyhW69cdTAwMTF0vVLQLbmN0Osuc1xiZoryzFxiwfxYz1x1MDAwYnB8sr5To8/b+9vn+uGgc+Nf3bqV5zSOu14tfMVVXHUwMDEyzFx1MDAxNN1cdTAwMTRUXHRUUlx1MDAxNFcudFx1MDAxMszo+zpo/UojqSBo1fBcdTAwMTSY0U12XHUwMDE4Y1Rb71WRxVx1MDAwN1TDruv3XHUwMDFlcLj88PeBN9rfaCxCpnPC01x1MDAxOZAxvNGhQTNT0PfAu1CZ042nnYG5rF6f9Fx1MDAxNDnwRP22eTb4uDKfe7PTdG72XHJ/lc49uaxcdTAwMTiyXa9dd0+ffbp71Xqp3pzNjlWtR2ikRmkq2WS6Q1x1MDAxMoeA0Vx1MDAxY6i0v6VcXGF1IVhFf1x1MDAxNN1NTbNUMVdcIlx1MDAxN6uMXHUwMDAzXG6TkWTeWJ2/Jfva7iCoX1VuXXHS8mS1+nSzceGtPy5cdTAwMTKs2Tf8IbBcblx1MDAxYkWcXHUwMDBmWItcdTAwMWSPwtifVMTRnHGG/5t46n+EVE61gydBcs2lMtSkgFxuTDuIca5cdTAwMTWXIFx1MDAxOYI9XHUwMDAzqVx1MDAxMlx1MDAxY7RyieaoIFx1MDAwNFx1MDAxOJgjbv/hQq0hXHUwMDFiv1x1MDAxMWbRX1x1MDAxMSpcdTAwMWVnjTBcdTAwMWKLUaX1K/ovyLhkmczn+aY68kXN/qSELGovXHUwMDA1xvlE2HCwUYuh38hcYppDXHUwMDEwj1x1MDAxZr6F2LhDUFx1MDAxM1x1MDAxYcLB2CRh4opEhC3bKH+7+Fx1MDAxN0TYinm6kFDQTHeAKU5cdTAwMTmXXHUwMDAyp2yCUJjNojBcdTAwMTRkXG6MoaJJXHUwMDEzilJcdTAwMGVww1x1MDAwNUr0KK9cdTAwMTVdXHUwMDEyXHUwMDExilx1MDAwMIdITjhcdTAwMGWuoIrquaZOfzdGwZlAO8zQLItdajN5NGJcdTAwMTRCJVx1MDAxMMpcdTAwMTaQPJXUxFK5v4pR8kVtdDYlZFx1MDAwYmKUXHUwMDE5mCDGXHUwMDFiMf5cdTAwMTFSScs+SiH3S61iV73yj3K09ZaBoSWINITO86+jlE4o3KOQ3lRcdTAwMWWGJ9ddX5jjrY31mYJ41lxiYYbij0DfPlx1MDAxNtl4zU9cbuVwapNBOGGgY6fH+cnI0Fx1MDAxZDOInGu8/jdjXGZGXHUwMDA0XHUwMDAxXHUwMDA1MitcXM9jXFw7SVx1MDAxOESBRvpcdTAwMTfzJ1xmpCGpf8RYjuTqLY51XHUwMDE0ZMbk0M1cdTAwMTRCS84oMVxc4T3mXHUwMDFjo1x1MDAwYoOHzFx1MDAwMFx1MDAxZI/kL1x1MDAxOZ9L9GkyXHUwMDE4h50oQNjWzq2/f98467uf/dNG+eik7H06nqk6USlAb1vj9Nl8qJhEXHUwMDE4XHUwMDE3jtbEXHUwMDEwXHUwMDFjXHUwMDFmxtMuQEQ8K3zNVphcYtTgUGfnwyTPrVx1MDAxN5bS8KRcIp+bQjZcdTAwMDZiNVx1MDAxOe/AV9XrzVx1MDAwMjBcdTAwMTVcdTAwMGLaLCHAbC9cblx1MDAxMHb8pbH5VK5t7YmDeudu/W67196bqf7XMFtXhVx1MDAxZTRcdTAwMDNFXGJyZFx1MDAxMmGUS4dLtIe5YprJWNlV3CxeoSzW/lxmWkxoPnK+MkCmeW7S2WahlGZcdTAwMGJcdTAwMDFcdTAwMTl+4Fx1MDAxZlx1MDAwMdnGKNkyXHUwMDEzztQyw+ytXHUwMDFmXHUwMDA1SPtyvDs8veo2qkFz/6jRXCJ9efnoz4I0XHUwMDE0IDtcdTAwMTScSmLrbFJcdTAwMGWohVx1MDAxYZFMXHUwMDAwVWhNcmlEXHUwMDFhanxlMMbbn1x1MDAwZTW0myhVXHUwMDE52Vx1MDAxZosnnVx1MDAwYjUjcSYosHfVd1x1MDAxNIVWXHKPR8reXHUwMDAxtPVGXHUwMDAyXHUwMDBl+TiTS42z125cdTAwMTTArDjJVaTQqISiZCywZDI27ZVxnlx1MDAxMSnmc62s+M1QZlx1MDAxNOWAXHUwMDA2VFZkXHUwMDE4VG5kmFx1MDAxMmZQn7GlKoJcdTAwMWZcdTAwMDOtV1x1MDAwZVx1MDAxYaX9oO1nou0nlkyw3IqJKWVcdTAwMGIp0EVdKkBeccaqXGJ52rCC1CqIqanVXHUwMDE18Fx1MDAxMu3PXHUwMDEwQVx1MDAwNaFQzrNzMjS/5kFcdTAwMDFcdTAwMTVKkkXUY/9o8jBcdTAwMDW8M88+rFf6v1x1MDAwMGBxLcIkXHUwMDAwM7pWXHUwMDAwxOzVXHUwMDFhKSBmL7amdFTmXHUwMDAwRlx1MDAwMlx1MDAwMZiIm6xqiVx1MDAxN4BFja6bIVwi26vLX1xiRilcdTAwMTj0696lXHUwMDAzXHUwMDBiK1x1MDAxYeT25vP5pl9lx61LP1x1MDAxON6UP1x1MDAxZFx1MDAxY29+vPqov+u2ee+45p6r+9Nm0+OeOruaQ6HE5s7N1nG9Xtlot731g51cdTAwMWJ/0G3LObRbvNLyPe1Oq8DIXHUwMDFl+KjZt08/qVxcqlhcZopir5SxfFxuWVUoL2R9XHUwMDE1QctIXHUwMDAzzXJXXHLJzW9cYoPUTPlSVVhkXHUwMDE2KNs104ixXqnRXHLuX6t2O14jLIXubcdb5trkKbq4sDZ5SqdcdTAwMGKw23JcdTAwMWIvn33P3VD1q8HW01X1TrvXM5c6asntXHUwMDFhXHUwMDAzXHUwMDAxoIgm8Vx1MDAxMqm3Wke73FxiUUpcckijXHUwMDE0rGpcdTAwMWRcdTAwMTdcdTAwMDJqxVxiqnfBs+xcdTAwMDJBcotcdTAwMWPsSkvFzbtSlsVLjOauwKcpxJP+l5frb1x1MDAxYuTb2flB2N577Dy9nFx1MDAwNLPq70qtL09vj1l/5/T88NCc3TyG4dZsj/tzXHUwMDE1bXYvZ1G0kotcIpyOVlxuXHUwMDE143TlOCfan6X0XGK9X6FYKlx1MDAwMmxvbfJByfA/XHUwMDFinV9CRbvcnvJcdTAwMTRVNqlAp/nG6/t3u8dXt8HWltmtPD9cdTAwMWTfVFRjZ6YyQC1I0f4pTJrE/imgUmijWjpoXHUwMDEy261cdTAwMThQgCjJKlx1MDAwM7T7W1x1MDAxOFx1MDAwMlxcM0ZcZpDVXHUwMDA2Klx1MDAwNVCUtqxYyMxcdTAwMWRUXHUwMDAw0qVcYt/ByGzAXHUwMDExWXOZosfzrSum0qFI/1x1MDAxMoBxjfKWWLmPUuVcYi0lXG4xJfhBTN1WXHUwMDAw25NcdTAwMGUzTKNcdTAwMDRL4Fx1MDAxNFx1MDAwNzfeoFFo7Fx0YbhSklx0LvT09vKQMHq+SVxmRM2lXHUwMDE082L2UEmUL37jtVxulfvNw8ODXHUwMDAxVJutzY5b/ZZVvlgmXHUwMDBlQSXLkFx1MDAwMLTGX8RES0NL3+tcdTAwMTfBXHUwMDAxO4qAY0+Z5FFcdObCdlEptoOmMFx1MDAxZTg4XHUwMDA1guLUo8c+yXh2XHUwMDAzOFwi0ctcdTAwMTdWkphMl3gwbVx1MDAxY/RcdTAwMDHQXHUwMDFhVUajs5+RhVx1MDAwNpRcdTAwMWXJcTyMRFx1MDAxNWlYLLy/orxJylx1MDAxM5xcdTAwMDGQjPj8yDJhueaHXHUwMDAwXHUwMDA0klHvSkv/XzBcdTAwMWUzXHUwMDBl1fhIhiE7XHUwMDExXHUwMDE2PZH9XHUwMDAxkFx1MDAwZbpSnFx1MDAxMlTdnFx1MDAxMJi6aVx1MDAxNLKmw1xy11x1MDAxNGx6XHUwMDEyx1sk2qMwoldUXCLMJorF9PbycDBqLoWAX0t4xVx1MDAxNlcpXq+NXHUwMDE0LZDxXGaRUqFcIpGxi97ozq6NIERpo4hgOEXfdcPC6K6YrouTXHUwMDFmytDRemyrXHUwMDE1uTFs0qFSylFcdTAwMDbNO3TMmSYyXWezyn78OKWhXG7UgmU6VEAgd0WnIVx1MDAxYy1cdTAwMDf9Lo8qJ8wxLVx1MDAxY1E9c8O702an2zv44l2sdzYuXppilf74sXY/XHUwMDEwlilst9ixK2w3RbJcdTAwMGJccvdkS1GKoNLhXHUwMDFlW2pbwE5ook8hp1VeJdn+XGb1SVx1MDAxY1xiN1Jl1ifFV/1O7rcrXGLVRL1rtfmiwz2Vllx1MDAxYpbqozzDV98mXHUwMDFhSqhcXP1S2PK63le/3Sv5QWm0XHUwMDEz7TKnVaZo+cmo0Fx1MDAwZvS5XHUwMDAwufVz/fl+43L4rV8vn+5w2G2fX2SYXHUwMDE2WYFaRVx1MDAxY1x1MDAwNKyxkUO0hVhcdTAwMTK5TNtVtUxRTrlBc0qlXHUwMDAztVI43Jbd2EVjaFwi6ozQXHUwMDEx4lx1MDAxZi1WNEhcdNi9PldcdTAwMGJIi6p7OeM2T5LlR4n0PjDjQnqKU8BcdTAwMTexXHUwMDFh7OOwXrbVYJPSKHKg/IHVYV+CxqfhvTw6NM3Lb+Kww7Y8pmfCI1x1MDAxNdRRUqCrXGJcdTAwMTJBOVx1MDAwMUdcdTAwMDQro8KugmeEXHUwMDFho9MrurV07JpcdTAwMTbUtIBejcpIb0rtXHUwMDEwhr4mXHUwMDAzo4HEt2ZfoTGFRlx1MDAxYtZgmdsvSZNbam9rn9B6IVx1MDAwYljMXHJoYqlcdTAwMGbVXCIu3dqxXFxxnN9SsurZXHUwMDEzP6w8XHUwMDBlbm87py1xsr4hLslw5qJcdTAwMDNcdTAwMWOMUTKTSElBU5Msv+c2l8lstF8qoEynl2yuSlx1MDAwZUpcdTAwMWZHXCIopEQuMsuIJORCkY5cdTAwMTZNiHclVFxuS1x1MDAwZa4/+aR+JKrDgVm/acpd//G4XHUwMDEy3zpgkuXHJ95bcZB9w6jZt08/yVW8eXw8vSj3Xm56w1O6vV/5cmrae7OX8XBCXHUwMDFkyu0u6ELZYrskooh2LOsqQjSalCqt5FaIKs1cdTAwMDFRXHUwMDA2XWKTXHUwMDA3qfzqXo4jzkC+qzLvneGt5tPt+dNcdTAwMDHb3nyp3fR2ypXq4Vx1MDAxNTU/IVxm9XMxVVx1MDAxY4crTIdcdTAwMTFDXHUwMDFkgf6XYkZJObH1rq22XHUwMDAxjadcdTAwMDHAroZcdTAwMTfp9WEg7EpcdTAwMTabPWWa2ddRZCFK2HXTIKgyhqA2nG9cdTAwMDXAb2Y4gkGAXHUwMDAwWo+Z8Vx1MDAxOZKfXHUwMDBms7lcdTAwMWTBKfy2+bByvqzZn7SURVxypuA4t42AZs6kXHUwMDEzh1DO7buu8PGJUlTE8nlvmSWDqs1w+2JcdTAwMTNjX1DAzNtcdTAwMDW/YFx1MDAxZqBi7iukXHUwMDE0plDH4lx1MDAxY1A0zGX8fUnDkVx1MDAwMkejXHUwMDFk7Lt+JFxulYC0ilx1MDAwNlx1MDAwNXanIGRcdTAwMWSDXG6aQFaAV4JDhc0sXG50ZuOb6a34JMUnKGxcdTAwMWGHOzveS9Kbjo7L+7iiXHUwMDE2aMtcdTAwMTRcdTAwMTia875iOYI2OjkpYktFJvZFSIBeXHJaucowXCJcdTAwMDXI9JtDQDjcVltcdTAwMWJNsYvUMP7r6CS4eFx1MDAwMP1y3XCrsHGwu7dcdTAwMTOEWzswU4JIgXI42LdiSZRcdTAwMWKZXFxIy9DgXHUwMDE3TGqJXHUwMDE2PdHxcEtUrbPaJVwi3v50wuBcdTAwMWM9KOCZcWSI43YydoVkbsiSvlx1MDAxOeDMbfe8kuuXPJTUmbaMWO6tWSb6U4C8o229vdF4kd3+yef95rNPh+u1zzNcdTAwMDWUOdeOQX9ccojdfdEkI8roXGY4XHUwMDE2kFxmmZMjSaY9XHUwMDAzllx1MDAxOUJeIa9IVds37FGVuaOf5ftcXOghtyul47mfOW3R8mHgXaCY2utLT26nP9OeSMu9V8tEf1xugFdcdTAwMWPnK1J5OI9cdTAwMDVRY7tSrThqvFpcdTAwMDCTaH+Wqnsq7W6a6cSMfV6ar/LQsqaGL+da0237XHUwMDAy3iXfMmJKemVcdTAwMTJ8sS5cdTAwMTVcdTAwMDAvO+Y3k8YjqiC4bEPHxcHlXHUwMDE18Fx1MDAxMu3PXHUwMDAyPIN6TejMYFx1MDAxN1P5e9haelx1MDAxNMq8651zXHUwMDBir1pww37X7Sw59KbkYVJVXHUwMDBi8U69gu+PN1x1MDAxZnjNfXg4XHUwMDBmcVx1MDAxNNe+e7ZrT23veSNDfFx0J+hxl2xcdTAwMDMj7Fr59eyc/PX3XHUwMDFmf/9cdTAwMGZcdTAwMWRJWIkifQ==Do all columnshave exactmatches?Do you wantthe nearest eventbefore or after?NoYesBeforeAfterAs-of JoinReverse As-of JoinDo you wantall rows fromthe left table?JoinWhat do youwant when thereis no match?NoYesRaise an errorUse null valueExact JoinNatural Join

Are all of your match columns exact matches?

  • When analyzing time series, it is common to join data immediately before or immediately after an event. Such inexact matches can be performed using the aj(As-of Join) and raj(Reverse As-of Join) methods. aj joins the closest data at or before an event. raj joins closest data at or after an event.

Should all data from the left table appear in the result?

  • Many joins include all data from the left table in the result. join is different. It only includes rows that have matching values in both tables.

How should multiple exact matches be handled?

How should zero exact matches be handled?

Inexact match joins (time-series joins)

When analyzing time series, you may want to know values immediately before or immediately after an event. Inexact matches join these approximate matches in the left and right table.

As-of Join

aj is used to join data from the right table immediately before or at the time of an event in the left table. If there is no matching key in the right table, appended row values are null. If there are multiple matches, only the closest match is returned.

Reverse As-of Join

raj is used to join data from the right table immediately after or at the time of an event in the left table. If there is no matching key in the right table, appended row values are null. If there are multiple matches, only the closest match is returned.

Exact match joins

There are several methods to join when values are equal. They differ based on:

  • if all rows from the left table are included,
  • how multiple matches are handled, and
  • how zero matches are handled.

Join

join performs an inner join and returns all possible combinations having matching records in both tables. Rows without matches in both tables do not occur in the result. Rows with multiple matches appear multiple times in the result.

from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT

left = new_table([
string_col("Letter", ["A", "B", "C"]),
int_col("Number", [5, 3, 2])
])

right = new_table([
string_col("Letter", ["A", "A", "B", "B", "D"]),
int_col("Code", [10, 12, 14, NULL_INT, 16]),
])

result = left.join(table=right, on=["Letter"])

Exact Join

exact_join joins on matching values from the right table. If there are zero matches or multiple matches, an error is raised.

from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT

left = new_table([
string_col("Letter", ["A", "B", "C"]),
int_col("Number", [5, 3, 2])
])

right = new_table([
string_col("Letter", ["A", "B", "C", "D"]),
int_col("Code", [10, NULL_INT, 16, 18])
])

result = left.exact_join(table =right, on=["Letter"])

Natural Join

natural_join joins on matching values from the right table. If there are no matches, joined values are null. If there are multiple matches, an error is raised.

natural_join is similar to exact_join, but no matches results in a null value instead of an error.

from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT

left = new_table([
string_col("Letter", ["A", "B", "C"]),
int_col("Number", [5, 3, 2])
])

right = new_table([
string_col("Letter", ["A", "B", "C", "D"]),
int_col("Code", [10, NULL_INT, 16, 18])
])

result = left.natural_join(table=right, on=["Letter"])