0 Kudos

Add right and full outer joins to JMP Query Builder

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.

Tracking Number:

Defect ID:

3 Comments
Community Manager

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 examplesData Wrangling QB join examples

 

Left outer join customer tableLeft outer join customer table

 

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

 

Screen Shot 2020-06-29 at 11.15.47 AM.png

 

 

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

Screen Shot 2020-06-29 at 11.18.22 AM.png

 

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.

Level VI

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. 

Community Manager

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