community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

How To: Replicate the WHERE EXISTS Functionality of SQL

Alteryx
Alteryx
Created on

Let's talk about how to replicate the WHERE EXISTS functionality of SQL within Alteryx.

 

Example 1:  UNION - Add rows from table#1 to table#2 if the key value of table#1 does not exist in table#2.  If desired, the combined data set could be joined with a third data set, but only if the key value in table#3 does not exist with only one key column.  

 

Example 2:  SELECT/UPDATE records from table #1 based on the contents of table #2.  The statement below generates the names of customers who had orders during 2016.

 

select c1.customer_number
      ,c1.customer_name
from customers c1
where     1 = 1
      and exists  (select *
                   from customer_orders c2
                   where      1 = 1
                         and c1.customer_number = c2.customer_number
                         and c2.order_year = 2016
                  )
;

 

 

The SQL EXISTS condition is used in combination with a subquery and is considered to be met if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.  

 

The syntax for the EXISTS condition in SQL is:  WHERE EXISTS (subquery)

 

The subquery is a SELECT statement. If the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the subquery does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.

 

 

where1.JPG 

 

 

Example 1:  Take the unmatched records from the source you are appending data from out of a join between the two data sets and union it back to the data set you are appending to.

 exists2.png

Example 2:  Create your "look up list" out of a filter for whatever you set as the condition.  In this example, a year that is in the data.  Join the filtered data back with the other data source. Deselect the data that comes from the filtered source.

 

Results:  

 exists3.png

Please see the attached workflow: Where Exists Question.yxmd.

Attachments