Question Re: Datasphere Graphical View Filter WHERE IN in Technology Q&A

Datasphere Graphical View Filter WHERE IN

hleboeuf — Wed, 23 Apr 2025 06:02:06 GMT

Hello,

When creating a SQL view its quite easy to write in a WHERE clause the code

WHERE Field_1 NOT IN (SELECT DISTINCT FIELD_11 FROM ATABLE).

How would would one create this in a performant way in a Graphical view ?

I know you could Join 2 Tables and then FILTER the result on a field being Blank/Empty, but is that performant. At this moment I see no posibility to do this easily.

Re: Datasphere Graphical View Filter WHERE IN

michal_majer — Wed, 23 Apr 2025 11:33:00 GMT

There is no direct equivalent to WHERE Field_1 NOT IN (SELECT DISTINCT FIELD_11 FROM ATABLE) in graphical view

The workaround is:

Create a LEFT JOIN between your tables (YourTable.Field_1 = ATABLE.FIELD_11)
Filter for NULL values in the joined table column (WHERE ATABLE.FIELD_11 IS NULL)

This produces the same results as NOT IN in most cases. Performance is actually often better with the LEFT JOIN approach than with NOT IN, especially for larger datasets.

Choosing SQL View in that case can be a good option - if you see that the logic will be simpler that way.

Re: Datasphere Graphical View Filter WHERE IN

aczeitnerBW — Thu, 24 Apr 2025 07:07:17 GMT

Actually I think I am not sure if I understand the question right but you absolutly can do this. When you put a filter operator in a graphical view you can do it

[Field to filter] in ( Select [Field to Filter] from [View] )

Sadly I 've just changed company and I do not have access to Datasphere for the moment to give you a proper answer but it is totally possible you just have to play with the syntax a little ( I had to use it also)

Re: Datasphere Graphical View Filter WHERE IN

michal_majer — Thu, 24 Apr 2025 07:46:24 GMT

Oh nice, indeed it works like that:
ID NOT IN (SELECT DISTINCT ID FROM TABLE)