Types of Joins-Tableau
Introduction: Joins enable us to get data from different tables provided that the tables have certain fields in common. The common field may be the primary key in one table that acts as a foreign key in another. Tableau allows us to perform joins in a very easy manner. It offers a guided approach to joining the two tables providing a couple of important options. Thus, related tables are joined or combined from the same or different sources to form a single virtual table which is an extended table having added columns from different tables. As complete data may not be available in one table, data is joined from multiple tables through common fields between them. It is also convenient for the users to use this joined table in data analysis works.
Rules to perform Joins:
The tables should have a common column between them.
Based on the common data type in both tables. If you create a join of tables with different data types, the join does not work.
Determine the right join type for your join to optimize the performance of Tableau with your data. If the join type you select is not appropriate for the tables, the join formed will not be proper and won’t give efficient data analysis results.
Types of Joins:
There are several types of joins from which we can select as per our requirement and situation while working with data in Tableau. Depending on the join conditions, Joins are divided into two types
a. Equi Joins: In the Join condition if you use the equality operator (“=”) then such a kind of Joins are known as “ Equi Joins”. Equi joins are divided into Inner, Outer and Self joins. The outer join is further divided into three types; Left outer, Right outer, and Full outer joins.
b. Non-Equi Joins: Joins that use operators other than equality operators such as <,>, <>, <=,>=, etc are known as "Non-Equi joins".
An example would be where we are matching the first name and then the last name, but we are checking where one field from a table does not equal a field from another table.
Joins are divided into the following types:
A. Equi Joins
Left Outer Join.
Right Outer Join.
Full Outer Join.
B. Non-Equi Joins
1. Inner Joins: An inner join returns the matching rows from the tables that are being joined. It’s like the intersection of two tables.
2. Cross Joins: Cross join produces rows that combine each row from the first table with each row from the second table.
3. Outer Joins: An outer join is an extended form of the inner join. It returns both matching and non-matching rows for the tables that are being joined.
Types of outer joins are as follows:
a. Left Outer Join: When a left join is formed between two tables, the resulting or joined table contains all the values from the left table and only matches or common values from the right table. If there are no joins or matches in the right table, NULL is being displayed.
b. Right Outer Join: When a right join is formed between two tables, the resulting or joined table contains all the values of the right table and only the matching values from the left table. The values that do not find a match in the left table are left as null in the resulting table.
c. Full Outer Join: When a full outer join is formed between two tables, the resulting table contains all the data values from both the left and right tables. Also, the values that do not find a match in both the tables are shown as null in the resulting table
4. Self-Join: The self-join is used to join a table itself. It means that each row of the table is combined with itself as well as with every other row of the table.
Common join issues :
If you don't see the results you expect after joining your data, you may need to do some additional cleaning on your field values. The following issues will result in Tableau Prep reading the values as not matching and excluding them from the join:
Different capitalization: My Sales and my sales
Different spelling: Hawaii and Hawai'i
Misspelling or data entry errors: My Company Health and My Company Heath
Name changes: John Smith and John Smith Jr.
Abbreviations: My Company Limited and My Company Ltd
Extra separators: Honolulu and Honolulu (Hawaii)
Extra spaces: This includes extra space between characters, tabbed spaces, or extra leading or trailing spaces