The JOIN statement

The JOIN statement is a prefix to the LOAD statement. It is used to join the table that is being loaded to a previously loaded table. The two tables are joined using a natural join, this means that the columns in both tables are compared and the join is made over those columns that have the same column names. This means that if multiple columns are shared between tables, the match will be made over the distinct combinations of those columns.
By default, QlikView performs an outer join. This means that the rows for both tables are included in the resulting table. When rows do not have a corresponding row in the other table, the missing columns are assigned null values.

Let us consider the following two tables:

The JOIN statement can be prefixed with the statements INNER, OUTER, LEFT, and RIGHT, which performs an inner, outer, left, or right join respectively. This has the following results:

• INNER JOIN: Only rows that can be matched between both tables will be kept in the result.
• OUTER JOIN: All rows will be kept in the result, rows that do not have a corresponding value in the other table will get null values for the fields that
  are unique to that table. When no prefix is specified, this is the default join type that will be used.
• LEFT JOIN: All rows from the first table and those rows from the second table that have a corresponding key in the first table, will be included in the result. When no match is found, null values will be shown for the columns that are unique to the second table.
• RIGHT JOIN: All rows from the second table and those rows from the first table which have a corresponding key in the second table, will be included in the result. When no match is found, null values will be shown for the columns that are unique to the first table.

 

Here are some details:
- Using the key word JOIN in QV is the same as FULL OUTER JOIN in SQL ;
- LEFT JOIN in QV is the same as LEFT OUTER JOIN in SQL ;
- RIGHT JOIN in QV is the same as RIGHT OUTER JOIN in SQL ;
- INNER JOIN in QV is the same as INNER JOIN in SQL ;
So, the big difference is that, in QV, default joins are FULL OUTER JOIN, but, in SQL, default joins are INNER JOIN.
Then, in SQL you can specify on which columns you want to join either from the right table, either from the left table. But QV automaticaly joins columns with the same name and like in SQL, the combination of all values from those columns have to be the same in the left table and in the right table to perform the join (whatever type of join you choose).

Wiki