Chartio is going away March 1, 2022
Documentation Visual SQL FAQs

Omit data using an anti-join in Visual SQL

At times, you may want to exclude rows where two joining Queries have matching values, only keeping the rows that didn’t match for a particular Query. In this case, we’ll be interested in keeping the rows from the Query on the left where they did not match with the Query on the right. This is called an anti-join, and the way to carry this out in Chartio is in the Pipeline.

In the following example, we used our Dundersign Demo Data data source to create a custom column that includes eight cities we’d like to exclude from our results. We performed the anti-join by doing the following:

  1. For your first Query, pull in the rows you’d like to exclude from your second Query’s result set. You need to add the same column twice since you’ll need to use the second column as a means to filter after joining the two Queries—if it’s not clear now, you’ll see what we mean a bit later.

    For our example, we added our custom column twice.

    Add the column with the values to exclude twice in your Query

  2. Add a second Query and include the column you’d like filtered, where you’ll only include the rows that don’t match with the city values from the Query in step 1. You also need to add this column twice in the Query.

    For our example, we want the list of cities where contacts were created after January 1, 2018.

    Click Run Query.

    Add a second dataset with the new column

  3. Click the Join Action below Query 2. By default, the Queries are merged with an Outer Join on the first columns, which is what we want in this case.

    This is where pulling in the same column twice helps us see where we have a match with the other Query’s result set. In our output, the second column includes the results you’re looking to exclude.

    Select Outer Join as the Merge type

  4. Add a Filter Action to exclude any rows with results in the second column.

    For our example, we excluded any rows where city_2 is not null. Click Save. Notice how our results return eight less rows now!

    Add a Filter Action to exclude the rows where the second column is not null

  5. Since they’re no longer needed, hide the duplicate columns.

    Here, we hid city_2 and Mailing City_2 so only city_1 remained in our result set.

    Hide the unnecessary columns

We now have successfully removed the cities in our custom column, reducing our 7,515 rows to 7,507 rows!

Anti-Join final result

Need more help?