Skip to main content Skip to complementary content
Qlik Resources

Join

The join prefix joins the loaded table with an existing named table or the last previously created data table.

The effect of joining data is to extend the target table by an additional set of fields or attributes, namely ones not already present in the target table. Any common field names between the source data set and the target table are used to work out how to associate the new incoming records. This is commonly referred to as a “natural join”. A Qlik join operation can lead to the resulting target table having more or fewer records than it started with, depending on the uniqueness of the join association and the type of join employed.

There are four types of joins:

Left join

Left joins are the most common join type. For example, if you have a transaction data set and would like to combine it with a reference data set, you would typically use a Left Join. You would load the transaction table first, then load the reference data set while joining it via a Left Join prefix onto the already loaded transaction table. A Left Join would keep all transactions as-is and add on the supplementary reference data fields where a match is found.

Inner join

When you have two data sets where you only care about any results where there is a matching association, consider using an Inner Join. This will eliminate all records from both the source data loaded and the target table if no match is found. As a result, this may leave your target table with fewer records than before the join operation took place.

Outer join

When you need to keep both the target records and all of the incoming records, use an Outer Join. Where no match is found, each set of records is still kept while the fields from the opposite side of the join will remain unpopulated (null).

If the type keyword is omitted, the default join type is an outer join.

Right join

This join type keeps all the records about to be loaded, while reducing the records in the table targeted by the join to only those records where there is an association match in the incoming records. This is a niche join type that is sometimes used as a means of trimming down an already pre-loaded table of records to a required subset.

Example results sets from different types of join operations

Example results sets from different types of join operations, with a before-and-after representation of the data tables following each operation. The inputs consist of a target table and an incoming dataset. The target table has two columns with two row entries for the fields 'Trade ID' and 'Asset Class'. The incoming data has two columns with two row eentries for the fields 'Trade ID' and 'Exchange'. The Left Join keeps the initial target table and adds a third column with everything blank except the addition of one of the 'Exchange' row entrties. The Inner Join create a table with one Trade ID row and a third column, again, for the 'Exchange' row entry. The Outer Join keeps the target table and adds a third column, along a third row to which the second row of the incoming dataset is appended. The Right Join removes the second row entry of the target table and replaces it with information from the second row entry of the incoming dataset, adding a third column in the process.
Information noteIf there are no field names in common between the source and target of a join operation, the join will result in a cartesian product of all rows – this is called a “cross join”.

Example result set from a "cross join" operation

Before-and-after comparison of input data with output after a cross join operation (which can involve a join operation of any type). The inputs are a target table and an incoming dataset. The target table consists of three columns, with two row entries. The columns are 'Trade ID', 'Base Currency', and 'Amount'. The incoming dataset has two columns ('Trade Currency' and 'Rate') and two row entries. The resulting table has five columns, which are 'Trade ID', 'Base Currency', 'Amount', 'Target Currency', and 'Rate'. There are four row entries, combining all of the data. Each 'Trade ID entry has two rows with different data in the other fields.

Syntax:  

[inner | outer | left | right ]Join [ (tablename ) ]( loadstatement | selectstatement )

Arguments
Argument Description
tablename The named table to be compared to the loaded table.
loadstatement or selectstatement The LOAD or SELECT statement for the loaded table.

These topics may help you work with this function:

Related topics
Topic Description
Combining tables with Join and Keep This topic provides further explanation of the concepts of “joining” and “keeping” data sets.
Keep The Keep load prefix is similar to the Join prefix, but it does not combine the source and target datasets. Instead, it trims each dataset according to the type of operation adopted (inner, outer, left, or right).

Example 1 - Left join: Enriching a target table with a reference data set

Example 2 – Inner join: Combining matching records only

Example 3 – Outer join: Combining overlapping record sets

Example 4 – Right join: Trimming down a target table by a secondary master dataset

Learn more

 

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here