Database Join
A self join or recursive join .. or is it ?
Workshop - Database Join
Searching for information in databases, text files, web services, and so on, is a very common task. In this workshop we're going to query the Products table for products are listed below a set buy price.
The database join isn't actually a join, but a series of queries against the table based on set conditions. Be aware this results in a performance hit.
Drag the Data grid step onto the canvas.
Open the Data grid properties dialog box.
Ensure the following details are configured, as outlined below:
Drag the Database Join step onto the canvas.
Open the Database Join properties dialog box.
Ensure the following details are configured, as outlined below:
The ‘Parameter fieldname’ is where you specify the parameters, therefore the values, for the conditions. Each row in the grid represents a comparison between a column in the table, and a field in your stream, by using one of the provided comparators.
LIKE matches values. You can't alias a column in the select clause and then use it in the where clause
The question marks you type in the SQL statement represent parameters. The purpose of these parameters is to be replaced with the fields you provide in ‘Parameter fieldname’. For each row in the stream, the Database join step replaces the parameters in the same order as they are in the grid, and executes the SQL statement.
So, let’s look at the WHERE conditions entered:
PRODUCTNAME LIKE like_statement and BUYPRICE < max_price
For the first record this translates as:
WHERE PRODUCTNAME LIKE concat ('%','Aston Martin','%') AND BUYPRICE < 90
As the Outer Join option is checked The FULL OUTER JOIN keyword returns all rows from the left table and from the right table. The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
The table dataset A is then compared with the stream dataset B. If there’s a match, then values for PRODUCTNAME and PRODUCTSCALE are returned.
This is not a database join. Instead of joining tables in a database, you are joining the result of a database query with a dataset.
For the second record:
WHERE PRODUCTNAME LIKE concat ('%','Ford Falcon','%') AND BUYPRICE < 70
As there is no record, NULL values are returned for:
PRODUCTNAME and PRODUCTSCALE.
So far, the results could be achieved using a Database Lookup step. However, there is a significant difference, as illustrated with the third row. For Corvette, the Database join found two matching rows in the database, and retrieved them both. Not possible with a Database lookup step.
Click the Run button in the Canvas Toolbar.
Click on the Preview tab:
Last updated
Was this helpful?