# Exclusion join query **URL:** \ **Category:** ksqlDB\ **Created:** [17 February 2022 01:04 UTC](https://forum.confluent.io/t/exclusion-join-query/4198 "2022-02-17T01:04:06Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![almindor](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/almindor/32/1597_2.png) [@almindor](https://forum.confluent.io/u/almindor)\ **Post date:** [17 February 2022 01:04 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/1 "2022-02-17T01:04:07Z")
How can I make an “exclusion join query”? I’m trying to do something like this from traditional SQL ```sql SELECT * FROM topic1 WHERE NOT EXISTS ( SELECT 1 FROM topic2 WHERE topic2.key1 = topic1.key1 AND topic2.key2 = topic2.key2 AND topic2.key2 = topic2.key2 ) ``` In a “after the time window” kind of logic. To give better perspective consider these records come into the two topics (topic is first column) ```nohighlight topic1,addressX,100,value1 topic2,addressX,100,value1 topic2,addressX,101,value2 ``` The goal of this query is to identify “dangling mismatching ends” per address. So in this case for example, we want to identify that topic1 is **missing** addressX matching value (after some time period of course) I tried doing this with a `LEFT JOIN` as well as a `FULL OUTER JOIN` but these don’t seem to wait for the other topic to get the data as defined by the window, as soon as one side has something the null variant join rows are output, so there’s no way to identify a dangling record. ---
**Author:** ![mjsax](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/mjsax/32/3113_2.png) [@mjsax](https://forum.confluent.io/u/mjsax)\ **Post date:** [17 February 2022 05:44 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/2 "2022-02-17T05:44:04Z")
Did you try a stream-stream windowed join with GRACE period? This join should wait after grace period passed, before emitting left/right join results. You need to be on the latest version though… Cf [Announcing ksqlDB 0.23.1 - New Features and Updates](https://www.confluent.io/blog/ksqldb-0-23-1-features-updates/#grace-period) ---
**Author:** ![almindor](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/almindor/32/1597_2.png) [@almindor](https://forum.confluent.io/u/almindor)\ **Post date:** [17 February 2022 05:46 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/3 "2022-02-17T05:46:49Z")
Hmm we’re on 0.21 right now but even so this is confusing to me. If the default grace period is 24h shouldn’t the LEFT/OUTER joins then hold on until that time? Did the actual logic change here? UPDATED: ah nevermind, you’re right! I didn’t read past the section where the logical change is explained. Thank you, this would fix it, but we’ll need to update to 0.23 then. ---
**Author:** ![mjsax](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/mjsax/32/3113_2.png) [@mjsax](https://forum.confluent.io/u/mjsax)\ **Post date:** [17 February 2022 16:36 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/4 "2022-02-17T16:36:05Z")
Correct. The old join behavior was weird, and thus we fixed it in 0.23. ---
**Author:** ![almindor](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/almindor/32/1597_2.png) [@almindor](https://forum.confluent.io/u/almindor)\ **Post date:** [17 February 2022 18:15 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/5 "2022-02-17T18:15:58Z")
We tried updating from [Docker Hub](https://hub.docker.com/layers/confluentinc/ksqldb-server/0.23.1/images/sha256-876380b16b35122f6c420111bdbce1302ca9d587345673a6d8604abb74b4cfd7?context=explore) which is the latest image released but the `GRACE` keyword is rejected on joins with `mismatched input 'GRACE' expecting 'ON'` I noticed tho that the server version reported when using this docker image is `0.23.1-rc9` and the image was posted on Dec 15th 2021 almost a month before the announcement of v0.23.1? Is this a mis-release? ---
**Author:** ![mjsax](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/mjsax/32/3113_2.png) [@mjsax](https://forum.confluent.io/u/mjsax)\ **Post date:** [17 February 2022 21:55 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/6 "2022-02-17T21:55:43Z")
Not sure. Did you update both server and client? ---
**Author:** ![almindor](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/almindor/32/1597_2.png) [@almindor](https://forum.confluent.io/u/almindor)\ **Post date:** [17 February 2022 22:42 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/7 "2022-02-17T22:42:17Z")
Yes, we’re using [Docker Hub](https://hub.docker.com/r/confluentinc/cp-ksqldb-cli/tags) and we’re on latest with `CLI v7.0.1, Server v0.23.1-rc9` ---
**Author:** ![almindor](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/almindor/32/1597_2.png) [@almindor](https://forum.confluent.io/u/almindor)\ **Post date:** [17 February 2022 22:48 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/8 "2022-02-17T22:48:38Z")
Here’s the error with the query I tried: ```auto line 4:19: mismatched input 'GRACE' expecting 'ON' Statement: SELECT t2.* FROM topic1 t1 LEFT JOIN topic2 t2 WITHIN 10 MINUTES GRACE PERIOD 10 MINUTES ON t1.field = t2.field EMIT CHANGES; Caused by: line 4:19: mismatched input 'GRACE' expecting 'ON' Caused by: org.antlr.v4.runtime.InputMismatchException ``` ---
**Author:** ![mjsax](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/mjsax/32/3113_2.png) [@mjsax](https://forum.confluent.io/u/mjsax)\ **Post date:** [18 February 2022 01:57 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/9 "2022-02-18T01:57:23Z")
Confluent `7.0` ships with ksqlDB 0.21 (cf [Supported Versions and Interoperability for Confluent Platform | Confluent Documentation](https://docs.confluent.io/platform/current/installation/versions-interoperability.html#ksqldb)) Can you try ksqlDB CLI 0.23.1: [Docker Hub](https://hub.docker.com/r/confluentinc/ksqldb-cli/tags) ---
**Author:** ![almindor](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/almindor/32/1597_2.png) [@almindor](https://forum.confluent.io/u/almindor)\ **Post date:** [18 February 2022 18:02 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/10 "2022-02-18T18:02:06Z")
That worked, but only syntax wise. The query is now not eager, but it never returns the null cases. E.g. if I have a single row in topic1 and nothing in topic2 the left join never returns anything. It returns matching records if there are any. I did `WITHIN 10 SECONDS GRACE PERIOD 10 SECONDS` and waited over a minute to be sure. Is there something I’m missing about how this is suppose to behave? My understanding is that if grace period times out from the leftside record being “buffered” it’d output it with nulls for the right side values (in case of the left join) ---
**Author:** ![mjsax](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/mjsax/32/3113_2.png) [@mjsax](https://forum.confluent.io/u/mjsax)\ **Post date:** [22 February 2022 16:11 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/11 "2022-02-22T16:11:09Z")
The observed behavior is as expected. Note that time is tracked based on event-time, not wall-clock time. Left/Right join results are only emitted when `stream-time` (max observed event-time) passed window close time (ie, window-end time plus grace period). If you stop sending data, `stream-time` cannot advance and thus you won’t observe output. ---
**Author:** ![almindor](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/almindor/32/1597_2.png) [@almindor](https://forum.confluent.io/u/almindor)\ **Post date:** [22 February 2022 18:12 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/12 "2022-02-22T18:12:17Z")
I see. I just didn’t have long enough output on either of the topics. Is there any way to detect a stall when one side completely stops sending tho? ---
**Author:** ![mjsax](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/mjsax/32/3113_2.png) [@mjsax](https://forum.confluent.io/u/mjsax)\ **Post date:** [23 February 2022 21:06 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/13 "2022-02-23T21:06:59Z")
Time progress is tracked across both inputs for the join case. As long as one input topic has input data, time will advance. ---
**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/confluentcommunity/original/1X/c49438c90c9df282e9996fdf6971be890c71b65a.svg) [@system](https://forum.confluent.io/u/system)\ **Post date:** [19 March 2022 01:04 UTC](https://forum.confluent.io/t/exclusion-join-query/4198/14 "2022-03-19T01:04:26Z")
This topic was automatically closed after 30 days. New replies are no longer allowed.