Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
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:

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.

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.





Community Manager
Status changed to: Archived
We are archiving this request. If this is still important please comment with additional details and we will reopen. Thank you!
Level XI

@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.



Level XI

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.