Datasphere Graphical View Filter WHERE IN

[hleboeuf]

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.

[aczeitnerBW · Accepted solution]

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)

[michal_majer · Accepted solution]

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

[michal_majer]

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

The workaround is:

1. Create a LEFT JOIN between your tables (YourTable.Field_1 = ATABLE.FIELD_11)
2. 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.