Why? Where are the usual Left and Right Joins for in-db?

[Atabarezz]

I'm sure no one want's to do double dipping on huge data sets even if it's in-db...

So can we have,left and right joins in the in-db join tool as well to further develop workflows from these two additional outputs?

Ps: the idea originally belongs to another Alteryx client mentioned this in IT central; https://www.itcentralstation.com/product_reviews/alteryx-review-38876-by-prometheus-tito-amoguis-ii

Best

[Joe_Mako]

My thought would be it is to get the best performance out of the technology, because even through they have a similar name, these tools have different inner workings.

My understanding is the regular Join tool is taking in a stream of records, comparing through records, and then sending the records out the output they fit. While the Join In-DB tool is authoring a SQL statement.

If for example your goal is to get records that are only in the Left Input (like the L output in a normal Join tool), then the Join In-DB can be configured to Left Outer Join, and then add a Filter In-DB to keep only when your key fields are Null.

Looking at https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ we can see that is how we write SQL normally, the In Left Only is a Left Outer Join combined with a Where Clause Filer to keep only when the joining fields are Null.

My perspective is that Alteryx provides us with elemental tools, and we can combine them together to create the precise logic we want. Join and Join In-DB work different because they are using different technology, and they are optimized for the technology they are using.

[Atabarezz]

I'm totally fine with authoring an SQL statement...
Actually you can build a single statement that can do multiple joins at once, here is a way;

SELECT dashboard_data.headline, dashboard_data.message, dashboard_messages.image_id, images.filenameFROM dashboard_data 
    INNER JOIN dashboard_messages 
        ON dashboard_message_id = dashboard_messages.id
    LEFT OUTER JOIN images
        ON dashboard_messages.image_id = images.image_id 

[ARich]

Thanks, @Joe_Mako-

Great explanation of why the Join In-DB tool works that way!

Best,

Alex

[cyeager]

Thanks for posting.  It helped me think of a solution in which in-database I used summarize, 'Left & Inner join' (because left join only isn't an option) Filtered to keep only the null fields of the new column, then I did another summarize and 'inner' joined that back.  All in database was how I needed to worked around it as I couldn't think of the SQL script to make it happen, and doing it out of database was too run-time consuming.