Skip to main content Skip to complementary content
Qlik Resources
Loading SearchUnify's search If you need assistance with your product, please contact Qlik Support.
Qlik Customer Portal
Loading SearchUnify's search If you need assistance with your product, please contact Qlik Support.
Qlik Customer Portal

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.

video thumbnail

Types of joins

There are four types of joins:

  • Left join

  • Inner join

  • Outer join

  • Right join

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.

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.

Left join

Diagram outlining, at a high level, the data combinations that occur during a left join.

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.

Inner join

Diagram outlining, at a high level, the data combinations that occur during an inner join.

Outer join (default)

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.

Outer join

Diagram outlining, at a high level, the data combinations that occur during 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.

Right join

Diagram outlining, at a high level, the data combinations that occur during a right join.

Cross join

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 and arguments

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.

Related topics

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).

Load script examples - Left join

A left join keeps all records from your main (target) table and adds matching data from a second (source) table. If there is no match in the source, the main table’s record still appears, but with empty fields from the source table.

Load script examples - Inner join

An inner join keeps only records that exist in both tables (based on matching field values).

Loac script examples - Outer join

An outer join keeps all records from both tables. Where there is no match, fields are left blank.

Load script examples - Right join

A right join keeps all records from the source table and adds matching info from the target table. If the target table has no match, fields from it are blank.

Troubleshooting - Cross join

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 – please let us know!

Leave your feedback here