How to create and use SQL joins and unions in Jitterbit App Builder
When creating Business Objects in the Business Logic Layer, it is often necessary to draw against two separate tables to display data from both. With App Builder, you can configure a Join type to combine columns from one or more tables, or Business Objects, to create a new Business Object. This article will walk you through the four kinds of SQL Joins supported in App Builder - Inner, Left Outer, Cross and Right Outer - and give examples on how to create and use them in App Builder. In addition, we will also take a look at another type of relationship called a Union that is available for configuration under Joins in App Builder.
- Inner - The Inner Join type returns rows that exist in each table
- Cross - The Cross Join produces a Cartesian Product
- Left Outer - A Left Outer Join returns all rows from the specified left table, regardless of a match on the right table
- Right Outer - A Right Outer returns all rows from the specified right table, regardless of a match on the left table
- Union - Combines the result-set of two or more SELECT statements
Inner join
What is an inner join?
Inner Joins are the most frequently used Join. When given two tables, an Inner Join's result set contains only the rows between the two tables that are the same, based on the Join columns.
Example
Lets say we want a Business Object of customers who have placed an order and the details of the order placed. This would be a great use case for an Inner Join, because an Inner Join can return records at the intersection of two tables. Our resulting set should include every CustomerID that has an OrderID. Below are the two tables Customer and Order which we will be joining.
Before creating your Inner Join, you need to make a Business Object. Ours is called Customer (with Order). Bring in the two tables into the Tables panel. Implement the Inner Join between Customer and Order. The Join column here is CustomerID which is the only column between the two that is the same.
The result set should be of the Customers who have placed Orders. In this case, our data resulted in one rows:
Cross join
What is a cross join?
A Cross Join produces a result set which is the number of rows x in the first table multiplied by the number of rows y in the second table. The result set will be x * y rows.
When trying to decide what makes sense with a Cross Join it helps to say the resulting set will have columns from table 1 for every column in table 2.
Example
We have two tables, Category and Supplier. We know that every Supplier would like every Category, so we need to make a Business Object that combines these two tables. We would like a resulting set which has a Category for every Supplier. This is a perfect example of when to use a Cross Join.
Below are example records for the tables Category and Supplier:
To Cross Join these two tables, we need to create a new Business Object. We called ours Supplier (and Categories). Bring in the two tables we would like to Cross Join under the Tables panel. Under the Joins panel, indicate you would like a Cross Join between the two tables.
Note
We don't need to indicate Join columns because, with the Cross Join, the two tables do not have to have columns in common to Join. We can still get a Category for every Supplier.
After implementing this Join, we will see results like this. Notice that there is a Category for every Supplier:
Left outer join
What is a left outer join?
A Left Outer Join fetches all matching rows of two tables (the inner between the two) plus rows that do not match any row in the second table. This is useful when you want all the results common between two tables but you also want the null values in the left table.
Example
You may want a list of all customers whether they placed an order or not. In this case, a Left Outer Join would be perfect. This example is similar to the previous Inner Join example except in this case we also want to see results where the there is a CustomerID listed without an OrderID.
Below are the tables we are using: Customer and Order. They are the same tables we used for the example of an Inner Join.
This Business Object declaration is very similar to the Inner Join example's business object. The only difference is the name and declaring this join as a Left Outer rather than an Inner Join.
The results give us the same results the Inner Join gave us, plus the rows in the Customer table that do not match any row in the Order table.
There are 92 results this time.
Right outer
What is a right outer join?
The third join is a Right Outer Join, which is just the opposite of a Left Outer. Using one or the other does not usually matter because you can always switch a table from the left to the right side of the join and it achieves the same effect. The results will vary depending on which join you choose and order of tables added to the Joins panel.
Example
As an example of wanting to switch from a Left Outer Join to a Right Outer Join, you may want to view all your Customers with or without Orders as well as all of the Order Details with or without Orders or Products. The below declaration is one way you could configure this logic.
When we run results, or view the quick grid results we see the following records returned:
Union
What is a union?
In App Builder, Union Joins produce a combination of two select statements which target the same Column. A Union Join combines the results of two different Columns (that have the same name) from two different Tables into one unified Column. While most Joins utilize a Join column, the Union Join does not. This is because other Joins require shared context between the two tables to function, but a Union Join only requires shared columns.
Example 1
For example, an Inner Join between the Product and Supplier tables would show every product along with their supplier, while a Union Join between the Product and Supplier tables would show you a list of all the different SupplierID fields. This is because the Union Join essentially shows all the results from the same column in two different tables, and these two tables only share the SupplierID. Remember with Joins, sharing context and having the same column are two different criteria.
Below are the two tables Supplier and Product.
Now you can create your Business Object that will be using the Union Join. We called ours Supplier (Union with Product). Bring in the tables you will be using into the Tables panel. Declare a Union between the two tables on the Joins panel. Again, this Join does not require Join Column. The Columns panel only allows us to add SupplierID as a column because it is the only column the two tables have in common.
The result set contains all the SupplierID's, including duplicate records if the SupplierID appears in both tables.
Example 2
Another use case for a Union is if a client asks you to find all the respondants, but the users can respond in multiple ways. Let's say a user can respond by email which is input into a table called EmailRespondants, but they can also respond through phone call using the PhoneRespondant table. You could Union the tables together to find the columns they have in common which could be RespondantID and RespondType to recieve a list of all the people who have responded and how they responded. This way you can also see the same RespondantID multiple times but be able to see how they responded each time.