Tableau Joins: Inner, Left, Right, Full Join | #Tableau Course #34

Summary

This video script explains how to join tables in SQL and Tableau. It covers the four types of SQL joins—inner, left, right, and full—demonstrating how each type combines data from two tables. The script uses customer data as an example to illustrate how matching and non-matching rows are handled differently in each join type. It also explains the concept of joins in Tableau, highlighting how to configure joins in the physical layer and define join keys. The content emphasizes the importance of choosing the right join type to avoid data loss and optimize performance.

Takeaways

• 😀 Joins combine fields (columns) from two tables into one big table based on a common key.
• 😀 In SQL joins, the left table is referred to as the 'left table' and the right table as the 'right table'.
• 😀 To perform a join, you need a key field that is present in both tables, and you must define the type of join.
• 😀 There are four types of joins: Inner Join, Left Join, Right Join, and Full Join.
• 😀 An Inner Join combines only the matching rows from both tables, excluding any unmatched rows.
• 😀 A Left Join includes all rows from the left table and matching rows from the right table. Unmatched rows from the right table appear as nulls.
• 😀 A Right Join includes all rows from the right table and matching rows from the left table. Unmatched rows from the left table appear as nulls.
• 😀 A Full Join includes all rows from both tables, with unmatched rows showing as nulls where there is no match.
• 😀 Be cautious when using joins, as selecting the wrong join type can result in data loss or missing information.
• 😀 Full joins can be slow and result in large tables, especially when there are many unmatched rows in both tables.
• 😀 In Tableau, joins are done at the physical layer, while the logical layer combines the data for visualization. You can customize the join type and key fields in Tableau.

Q & A

What is the purpose of using a JOIN between two tables?

A JOIN is used to combine data from two tables based on a common key field. This allows you to create a larger, unified table that contains columns from both tables.

What are the two sides in a JOIN operation?

In a JOIN operation, the two sides are referred to as the left table and the right table. The left table is considered the starting point for the operation, and the right table is where the data is matched.

What does the INNER JOIN do?

An INNER JOIN returns only the rows where there is a matching key in both tables. If no match is found for a row, it is excluded from the final result.

What happens when you use a LEFT JOIN?

A LEFT JOIN returns all rows from the left table, along with matching rows from the right table. If there is no match for a row in the right table, NULL values are returned for the right table’s columns.

How does a RIGHT JOIN work?

A RIGHT JOIN returns all rows from the right table, with matching rows from the left table. If no match is found in the left table, NULL values are returned for the left table’s columns.

What is a FULL JOIN and when is it used?

A FULL JOIN combines all rows from both the left and right tables. If there is no match for a row in one table, NULL values are returned for the missing side. It is used when you want to include all data from both tables, even if there are no matching keys.

Why are FULL JOINs slower than other types of JOINs?

FULL JOINs are slower because they include all rows from both tables, including unmatched rows, which can result in larger datasets and more processing time, especially when there are many unmatched rows.

What is the role of keys in a JOIN operation?

Keys are fields that exist in both tables and are used to match rows between them. Without a key, a JOIN operation would not have a basis for combining the tables.

How do joins work in Tableau's logical and physical layers?

In Tableau, the physical layer handles the actual joining of tables, and the logical layer contains a combined logical table resulting from those joins. The logical table is what Tableau uses for visualization and querying.

How can you customize a JOIN in Tableau?

In Tableau, you can customize a JOIN by selecting the type of JOIN (inner, left, right, or full outer) and specifying the key fields used for the join. You can also use multiple fields as keys by selecting them in the join setup.