Menu
Amazon QuickSight
User Guide

Joining Tables

If you want to use fields from multiple tables in your data set, you can use Amazon QuickSight's join interface to join two or more tables from the same data source. The join interface lets you specify the join type and the fields to use to join the tables. The fields used in the join must be from the data source and not calculated fields. The join interface doesn't let you use any additional SQL statements to refine the data set, so if you want to do this, use a custom SQL query instead. For more information about using a SQL query to create a data set, see Using a SQL Query.

To successfully join tables, make sure these requirements are in place:

  • The target of the join is a SPICE data set.

  • Both data sets are based on the same SQL database data source.

To join tables from different data sources, create the join before importing to Amazon QuickSight.

Important

If you have chosen a table and made any changes to the fields (for example, changing a field name or adding a calculated field), these changes are discarded when you add tables using the join interface.

Creating a Join

Use the following procedure to join several tables to use in a data set.

  1. On the data preparation page, expand the Tables pane and then choose a table. This will be the left-hand table when you are choosing a join type and join columns. The table appears in the join interface.

    Note

    You can only join tables if they are members of the same SQL database data source. Others don't appear in the list.

  2. In the Tables pane, choose another table. This will be the right-hand table when you are choosing a join type and join columns. The table appears in the join interface and a join appears between the two tables.

  3. Choose the join to open the Configure join pane.

  4. Enter the join column information.

    • In the Data sources section of the Configure join pane, choose the join column for the left-hand table. This should be a column that has a matching column in the right-hand table. For example, a Customers table will usually have a customer ID column to uniquely identify the customer, and an Orders table will usually have a Customer ID column to identify what customer that order belongs to.

    • Choose the join column for the right-hand table.

    • (Optional) If the tables you selected join on multiple columns, choose Add a new join clause and specify the next set of join columns. Repeat until you have identified all of the join columns for the tables.

  5. In the Configure join pane, choose a join type.

    You can choose from the following join types:

    • Inner — An inner join records only where both tables have matching values in the join columns. This is the most commonly used join type. For example, if you perform an inner join on the following Safety Ratings and Widgets tables:

      Copy
      rating_id safety_rating 1 A+ 2 A 3 A- 4 B+ 5 B
      Copy
      widget_id widget safety_rating_id 1 WidgetA 3 2 WidgetB 1 3 WidgetC 1 4 WidgetD 2 5 WidgetE 6 WidgetF 5 7 WidgetG

      In the result set, widgets without safety ratings aren't shown, and safety ratings that aren't associated with any widget aren't shown.

      Copy
      rating_id safety_rating widget_id widget safety_rating_id 3 A- 1 WidgetA 3 1 A+ 2 WidgetB 1 1 A+ 3 WidgetC 1 2 A 4 WidgetD 2 5 B 6 WidgetF 5
    • Left — A left outer join returns all records from the left-hand table, and only records that have a value in the join column for the right-hand table. For example, if you perform a left outer join on the Safety Ratings (left-hand table) and Widgets (right-hand table) tables, all safety ratings records are returned, and only matching widget records are returned:

      Copy
      rating_id safety_rating widget_id widget safety_rating_id 1 A+ 2 WidgetB 1 1 A+ 3 WidgetC 1 2 A 4 WidgetD 2 3 A- 1 WidgetA 3 4 B+ 5 B 6 WidgetF 5
    • Right — A right outer join returns all records from the right-hand table, and only records that have a value in the join column for the left-hand table. For example, if you perform a right outer join on the Safety Ratings (left-hand table) and Widgets (right-hand table) tables, all widget records are returned, and only matching safety ratings records are returned.:

      Copy
      rating_id safety_rating widget_id widget safety_rating_id 3 A- 1 WidgetA 3 1 A+ 2 WidgetB 1 1 A+ 3 WidgetC 1 2 A 4 WidgetD 2 5 WidgetE 5 B 6 WidgetF 5 7 WidgetG
    • Outer — A full outer join returns all records from both tables, regardless of the values in the join columns. This type of join can return very large result sets because it includes all rows from both tables. For example, if you perform a full outer join on the Safety Ratings and Widgets tables, all records are returned:

      Copy
      rating_id safety_rating widget_id widget safety_rating_id 1 A+ 2 WidgetB 1 1 A+ 3 WidgetC 1 2 A 4 WidgetD 2 3 A- 1 WidgetA 3 4 B+ 5 B 6 WidgetF 5 5 WidgetE 7 WidgetG
  6. Choose Apply.

    The join icon updates to indicate that the join type and columns have been selected.

    The fields from the right-hand table appear at the bottom of the Fields pane.

  7. (Optional) Repeat steps 2-6 to add more tables. The table you selected in Step 1 will be the left-hand table and the new table will be the right-hand table for each join you add.

Modifying Existing Joins

To modify an existing join, select the join icon to open it in the Configure join pane.

To remove a table from the data set, either deselect it in the Tables pane, or hover over it in the relationship pane and then choose the delete icon.