# SQL LEFT JOIN with WHERE issue **URL:** \ **Category:** SQL\ **Created:** [December 17, 2020, 10:46am UTC](https://forum.opensearch.org/t/sql-left-join-with-where-issue/4415 "2020-12-17T10:46:42Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![j.f](https://avatars.discourse-cdn.com/v4/letter/j/ccd318/32.png) [@j.f](https://forum.opensearch.org/u/j.f)\ **Post date:** [December 17, 2020, 10:46am UTC](https://forum.opensearch.org/t/sql-left-join-with-where-issue/4415/1 "2020-12-17T10:46:42Z")
I have this issue, reproduced simply bellow: Create 2 indices `test-a` and `test-b`. Each has a unique record, not existing on the other. ```nohighlight ESHOST=http://localhost:9200 CURLOPT="-s -u admin:admin" # create test-a with 3 records: 1, 2, 3 -- not including record 4 curl --location --request POST '${ESHOST}/test-a/_bulk?pretty' \ $CURLOPT \ --header 'Content-Type: application/json' \ --data-raw '{"index":{"_id":"1"}} {"iCCID":"1"} {"index":{"_id":"2"}} {"iCCID":"2"} {"index":{"_id":"3"}} {"iCCID":"3"}' # create test-b with 3 records: 1, 2, 4 -- not including record 3 curl --location --request POST '${ESHOST}/test-b/_bulk?pretty' \ $CURLOPT \ --header 'Content-Type: application/json' \ --data-raw '{"index":{"_id":"1"}} {"iCCID":"1"} {"index":{"_id":"2"}} {"iCCID":"2"} {"index":{"_id":"3"}} {"iCCID":"4"}' ``` LEFT JOIN on those indices seemed promising at first: ```nohighlight ESHOST=http://localhost:9200 CURLOPT="-s -u admin:admin" curl --location --request POST '${ESHOST}/_opendistro/_sql' \ $CURLOPT \ --header 'Content-Type: application/json' \ --data-raw '{"query" : "SELECT a.iCCID iCCID, b.iCCID iCCID_b FROM test-a a LEFT JOIN test-b b ON a.iCCID = b.iCCID"}' ``` Response from LEFT JOIN looks correct: ```json { "schema": [ { "name": "a.iCCID", "alias": "iCCID", "type": "text" }, { "name": "b.iCCID", "alias": "iCCID_b", "type": "text" } ], "total": 3, "datarows": [ [ "1", "1" ], [ "2", "2" ], [ "3", null ] ], "size": 3, "status": 200 } ``` So I go on trying a LEFT JOIN, this time adding a WHERE condition: ```nohighlight ESHOST=http://localhost:9200 CURLOPT="-s -u admin:admin" curl --location --request POST '${ESHOST}/_opendistro/_sql' \ $CURLOPT \ --header 'Content-Type: application/json' \ --data-raw '{"query" : "SELECT a.iCCID iCCID, b.iCCID iCCID_b FROM test-a a LEFT JOIN test-b b ON a.iCCID = b.iCCID where b.iCCID is null"}' ``` Response from LEFT JOIN with WHERE condition: ```json { "schema": [ { "name": "a.iCCID", "alias": "iCCID", "type": "text" }, { "name": "b.iCCID", "alias": "iCCID_b", "type": "text" } ], "total": 3, "datarows": [ [ "2", null ], [ "3", null ], [ "1", null ] ], "size": 3, "status": 200 } ``` **Here, i was expecting the query to return just one record (record 3), but received an unexpected result.** I tried other approaches using SQL, such as subquery, nothing worked for me. So I am posting here in hope of advise, or just in case somebody has a better way of approaching this. ---
**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\ **Post date:** [January 15, 2021, 10:39pm UTC](https://forum.opensearch.org/t/sql-left-join-with-where-issue/4415/2 "2021-01-15T22:39:08Z")
@j.f Thanks for reporting the issue! Unfortunately, post-filter/aggregate after join operation is not supported yet. We’re evaluating and will be adding more comprehensive JOIN support in our new query engine in future. Thanks! ---
**Author:** ![Jonas](https://avatars.discourse-cdn.com/v4/letter/j/aeb1de/32.png) [@Jonas](https://forum.opensearch.org/u/Jonas)\ **Post date:** [March 1, 2021, 3:45pm UTC](https://forum.opensearch.org/t/sql-left-join-with-where-issue/4415/3 "2021-03-01T15:45:32Z")
@daichen Are you sure? I have a similar problem and found this in the documentation where it explicitly says that one can use the SELECT FROM LEFT JOIN WHERE clause within certain contraints: [[Complex Queries - Open Distro Documentation](https://opendistro.github.io/for-elasticsearch-docs/docs/sql/complex/)] ([Complex Queries - Open Distro Documentation](https://opendistro.github.io/for-elasticsearch-docs/docs/sql/complex/)) ---
**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\ **Post date:** [March 1, 2021, 9:34pm UTC](https://forum.opensearch.org/t/sql-left-join-with-where-issue/4415/4 "2021-03-01T21:34:18Z")
An issue for your reference: [LEFT JOIN with WHERE doesn't filter the result as expected · Issue #124 · opendistro-for-elasticsearch/sql · GitHub](https://github.com/opendistro-for-elasticsearch/sql/issues/124) ---
**Author:** ![Jonas](https://avatars.discourse-cdn.com/v4/letter/j/aeb1de/32.png) [@Jonas](https://forum.opensearch.org/u/Jonas)\ **Post date:** [March 2, 2021, 9:42am UTC](https://forum.opensearch.org/t/sql-left-join-with-where-issue/4415/5 "2021-03-02T09:42:42Z")
@daichen Thank you for the reply! So is there no way of postfiltering? After a Join-clause? ---
**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\ **Post date:** [March 2, 2021, 4:51pm UTC](https://forum.opensearch.org/t/sql-left-join-with-where-issue/4415/6 "2021-03-02T16:51:13Z")
Yes, this is what we want to improve in the SQL Engine V2: [GitHub - opendistro-for-elasticsearch/sql: 🔍 Open Distro SQL Plugin](https://github.com/opendistro-for-elasticsearch/sql#sql-engine-v2). The JOIN in new engine should be able to support multi-join, post-filter/agg/sort etc. Please stay tuned to our GitHub repo. Thanks!