Amazon QuickSight
User Guide

Joining Tables

You can use the join interface in Amazon QuickSight to join tables from one or more schemas in the same data source. On the join interface, you can 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. You can't join on calculated fields that you created in Amazon QuickSight.

If you need to refine the SQL statement that you used to create the data set, 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 join tables from different data sources, create the join before importing to Amazon QuickSight.

Important

If you choose a table and make changes to the fields, these changes are discarded when you add tables by using the join interface. Examples of changes might include changing a field name or adding a calculated field.

Creating a Join

Use the following procedure to join tables to use in a data set. Before you begin, import or connect to your data.

  1. On the data preparation page, choose Add data near the top of the page.

    (Optional) If you're connected to a SQL-based data source, you can write a custom query by choosing Switch to custom SQL mode. Choosing this opens the following screen, where you can paste or type in a query. However, switching between modes erases your work in the other view. If you switch to custom SQL mode, choose Switch to diagram mode to switch back. The option to switch between SQL mode and diagram mode are available only when you are creating a new data set. If you want to switch modes for an existing data set, create a new one instead.

    The following steps use the diagram mode.

    After you choose Add data, the following screen appears.

  2. Choose the schema and the table that you want to add.

    If you don't immediately see your table in the list, you can search for it by entering text in the search box. Doing this searches the schema that you selected for the partial table name.

  3. Choose a table from the list by choosing the name of the table or the radio button next to it. Then choose Select. To exit without making changes, choose Cancel.

    After you add a table, you can interact with it by hovering over it. Some icons appear on the graphic. To see information about the table, choose the information icon ( ). To remove the table from your data set, choose the delete icon ( ).

    You can drag and drop tables to rearrange them.

    You can add a table multiple times, to create self joins. To help you tell two instances of the same table apart, it displays a counter after the name. An example is Product, Product (2), and Product (3). Field names in the Fields or Filters sections include a label so you can see which instance of the table a particular field is from.

    After you have added all of the necessary tables, you need to join them. You can see which joins aren't yet configured, because they display double red dots rather than a join symbol.

  4. Create a join by choosing the double dots ( ) to open the Join Configuration pane.

  5. Choose the columns to join the tables on.

    At the bottom of the screen, you can see options to set a field in one table equal to a field in another table.

    1. In the Join clauses section, choose the join column for each table.

    2. (Optional) If the tables you selected join on multiple columns, choose Add a new join clause. Doing this adds another row to the join clauses, so you can specify the next set of columns to join. Repeat this process until you have identified all of the join columns for the tables.

  6. In the Join configuration pane, under Join type, choose the kind of join to apply. You can choose each join type to learn more about it. For more information on joins, see Join Types.

  7. Choose Apply to confirm your choices. To cancel without making changes, choose the X at top right to close the Join configuration panel.

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

  8. (Optional) Choose one or more options at the far left to help you configure your data set:

    • For Data source, you can choose SPICE to store your data set in SPICE, or choose Query to pull live data.

      • If you choose SPICE, the data is ingested into SPICE, and visuals that use the data set run queries in SPICE, instead of against the database.

      • If you choose Query, the data is not ingested into SPICE, and visuals that use the data set run queries against the database, instead of in SPICE.

    • In the Fields section, you can choose fields you want to include or exclude.

    • In the Filters section, you can add or edit filters.

Join Types

Amazon QuickSight supports the following join types:

  • Inner joins

  • Left and right outer joins

  • Full outer joins

Let's take a closer look at what these join types do with your data. For our example data, we're using the following tables named widget and safety rating.

SELECT * FROM safety-rating rating_id safety_rating 1 A+ 2 A 3 A- 4 B+ 5 B SELECT * FROM WIDGET 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

Inner Joins

Use an inner join ( ) when you want to see only the data where there is a match between two tables. For example, suppose that you perform an inner join on the safety-rating and widget tables.

In the following result set, widgets without safety ratings are removed, and safety ratings without associated widgets are removed. Only the rows that match perfectly are included.

SELECT * FROM safety-rating INNER JOIN widget ON safety_rating.rating_id = widget.safety_rating_id 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 and Right Outer Joins

These are also known as left or right outer joins. Use a left ( ) or right ( ) outer join when you want to see all the data from one table, and only the matching rows from the other table.

In a graphical interface, you can see which table is on the right or the left. In a SQL statement, the first table is considered to be on the left. Therefore, choosing a left outer join as opposed to a right outer join depends only on how the tables are laid out in your query tool.

For example, suppose that you perform a left outer join ( ) on safety-rating (the left table) and widgets (the right table). In this case, all safety-rating rows are returned, and only matching widget rows are returned. You can see blanks in the result set where there is no matching data.

SELECT * FROM safety-rating LEFT OUTER JOIN widget ON safety_rating.rating_id = widget.safety_rating_id 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

If you instead use a right outer join ( ), call the tables in the same order so safety-rating is on the left and widgets is on the right. In this case, only matching safety-rating rows are returned, and all widget rows are returned. You can see blanks in the result set where there is no matching data.

SELECT * FROM safety-rating RIGHT OUTER JOIN widget ON safety_rating.rating_id = widget.safety_rating_id 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

Full Outer Joins

These are sometimes called just outer joins, but this term can refer to either a left outer, right outer, or full outer join. To define the meaning, we use the complete name: full outer join.

Use a full outer join ( ) to see data that matches, plus data from both tables that doesn't match. This type of join includes all rows from both tables. For example, if you perform a full outer join on the safety-rating and widget tables, all rows are returned. The rows are aligned where they matched, and all extra data is included on separate rows. You can see blanks in the result set where there is no matching data.

SELECT * FROM safety-rating FULL OUTER JOIN widget ON safety_rating.rating_id = widget.safety_rating_id 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

On this page: