606592_en-US

liliconcom

How to perform Anti-Joins?

Hi all,

 

a customer asked me about anti-joins. A left anti join, to be more precise:

"A left anti join : This join returns rows in the left table that have no matching rows in the right table."

(https://mode.com/blog/anti-join-examples/)

 

I could not figure how to do this in the interface (neither JOIN nor Query Builder).

Could somebody please enlighten me?

 

Thanks,

Florian

Re: How to perform Anti-Joins?

Hi Florian,

Would compare data tables give the functionality your user is after?

Malcolm

Re: How to perform Anti-Joins?

This could be done in a two-step process. Go for a full outer join (include non-matches on both the main and the "with" table), but be sure to check the "Match Flag" checkbox. The Match Flag column that is created can be used to tell you which table the data came from. The rows with only "Main" are the anti-join rows they are looking for.

 

Re: How to perform Anti-Joins?

Hi,

For me, it's a 2-step process. Perform the join (including non-matches from the main table) then select the rows where a value from the "with" table is missing/empty. These selected rows should be the rows where there are no matches in the main table.

This seems to match the solution in the blog post mentioned.

Re: How to perform Anti-Joins?

Thank you all! I think this is the way to go @Dan_Obermiller @Ryan_Gilmore . Also great suggestion @malcolm_moore1 , didn't even think about this, but could be valuable as well!

 

Best

Florian

Re: How to perform Anti-Joins?

This may be a bit convoluted but You could do a custom SQL query like:

SELECT * 
FROM "jmptable "  t1
WHERE t1.IDcolumn NOT IN (SELECT t2.IDcolumn FROM "jmptable2" t2);

Where jmptable is the name of the first table, jmptable2 is the name of the second table, IDcolumn is the key for the first table and second table.

You could use JMP Query Builder under tables. Select the two tables, use the standard left outer join on the two tables, then click on build query. After that, go too red triangle and chose convert to custom SQL and use above code.

Or you could write the JSL directly.

I think this is a good which list to support more types of joins in Query Builder and Join platforms.

Re: How to perform Anti-Joins?

Hi Florian,

Using JMP Query Builder you perform a left join with t1 and t2. where t1.id = t2.id and use where clause on the t2.id is null.

This query will return only values in table 1 (t1) but not in table 2( t2).

cheers,

Stan

Anti-Join with JMP Query BuilderAnti-Join with JMP Query BuilderAnti-Join with JMP Query Builder

 

SELECT t1.id, t1.t1_value 
FROM "untitled 1201"  t1 
 LEFT OUTER JOIN "untitled 1202" t2 
  ON  ( t1.id = t2.id )  
WHERE  (  (  ( t2.id IS NULL  )  )  ) ;