Add right and full outer joins to JMP Query Builder

[MathStatChem]

JMP Query Builder (Tables > JMP Query Builder) is missing a key functionality, allowing right and full outer joins.  See this techical note:  https://www.jmp.com/support/notes/59/817.html

It would be good to add the ability to do these types of joins with the JMP Query Builder.  This capability does exist, I know, with Tables > Join, but for those who want to use an SQL approach for table joining, this is an important capability to have.

[stan_koprowski]

Hi @MathStatChem ,

You can already do both types of joins--

A right-outer join is just dependent on the order of the primary table. If you swap tables then you have a right-outer join.

Full-outer join is selecting non-matches from both tables.

 Data Wrangling QB join examples

Left outer join customer table

Full outer join edit join select non-matches from both table

See attached after swap of primary and secondary tables; same results.

Inner join matches all records that are common between both tables

Left join matches all records from the left table and the related records from the right table.

Right join matches all the records from the right table and the related records from the left table.

FULL JOIN gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table.

[MathStatChem]

You can’t do a full outer join, see the technical note in the OP. I get that you can reverse the tables and do a left join. But that’s not intuitive. Tables > Join has the full left/right/inner/outer joining capability, seems reasonable JMP Query Builder should have this also. 

[stan_koprowski]

Hi @MathStatChem,

Please accept my apologies I was referring to using JMP Query Builder using database tables and you are referring to using QB for joining of JMP tables.

I should have read the original post more carefully where you do specifically call out JMP QB from the tables menu.

cheers,

Stan

[Ryan_Gilmore]

We are archiving this request. If this is still important please comment with additional details and we will reopen. Thank you!

[hogi]

@stan_koprowski , I wonder what was the reason to disable this feature in Jmp QueryBuilder?

"Include non-matching rows from 2nd. data table" is greyed out - always. Just to remind the user that this is not possible?

so, use Tables/Join instead?

... but there are no aggregation options.

[hogi]

Thanks @Jeff_Perkinson for the link/explanation:Supported Join types for Table > JMP Query Builder 

JMP uses SQLite as its database engine for Table > JMP Query Builder. SQLite supports only the following Join types:

• Inner-Join: returns rows when there is a match in both tables
• Left-Join: returns all rows from the left table, even if there are no matches in the right table

So, it will be hard to close this gap to other competitors.