Main Content

Join Tables Using Database Explorer App

You can select and import data from multiple tables using the Database Explorer app. First, you must join tables, and then select the data to import. You can join tables using different join types that depend on the database.

Different Join Types

The Database Explorer app creates an inner join by default. To use another join type, click the corresponding button in the Edit section of the Join tab.

Four join types with the Inner join type selected

There are four join types:

  • — An inner join retrieves records that have matching values in the selected column of both tables.

  • — A full join retrieves records that have matching values in the selected column of both tables, and unmatched records from both the left and right tables.

  • — A left join retrieves records that have matching values in the selected column of both tables, and unmatched records from the left table only.

  • — A right join retrieves records that have matching values in the selected column of both tables, and unmatched records from the right table only.

Join Tables

The Database Explorer app performs joins in one of two ways. The app can join tables automatically when you select tables by using shared columns (for example, the primary keys), or you can select tables without shared columns and manually specify the names of columns to match.

Automatic Join

The Database Explorer app can join tables automatically when you select tables in the Data Browser pane. In this case, the app checks if the selected tables have any column names in common. If there is a match, the app performs these steps.

  1. Opens the Join tab.

  2. Adds a join for each matched column name, creates an SQL query with the added joins, and executes the SQL query.

  3. Displays the SQL query in the SQL Query pane and the query results in the Data Preview pane.

If the app does not find a match, the app displays an error dialog box that directs you to select a table in the Join tab. The app also removes the selections from the tables in the Data Browser pane.

Manual Join

To join tables manually, you must know the names of each table and the names of the shared columns in the tables. Use these steps as a general workflow for joining tables.

  1. After connecting to a database, select a table in the Data Browser pane. In the Join section, click Join to display the Join tab in the toolstrip. In the Add section, the name of the table selected in the Data Browser pane appears in the left Table list.

    From the left Column list, select the name of the shared column.

    The left side of the Join tab shows the table inventoryTable for the Table selection and the productNumber column for the Column selection.

  2. From the right Table list, select the name of the table to join. From the right Column list, select the name of the shared column for this table.

    The right side of the Join tab shows the table productTable for the Table selection and the productNumber column for the Column selection.

  3. In the Add section, click Add Join. The SQL Query pane updates the SQL query with the new join. If the Automatic Preview button (located in the Preview section of the Database Explorer tab) is toggled on, the Data Preview pane displays the updated SQL query results automatically. The Join Diagram pane displays a pictorial representation of the join between the selected tables.

    The Database Browser pane shows the selected table inventoryTable in the Database Explorer app. The SQL Query pane shows the SQL SELECT query that selects productNumber, quantity, price, and inventorydate columns from the inventorytable and producttable database tables. The Data Preview pane shows the first 10 rows of data returned from the query.

  4. To add another join, select another table and column name combination in the left and right lists. Then, click Add Join again.

  5. In the Edit section, click one of the join types (for example, ) to specify a different join type, if necessary.

  6. To remove a join, select it in the list of joins in the Edit section, and click Remove Join.

    Note

    To change the order of joins, remove existing joins and create joins in another order.

  7. In the Close section, click Close Join to close the Join tab.

Join Diagram

After you join at least two tables, the Join Diagram pane displays a pictorial representation of the joins between tables. Each blue circle shows the join type. Each green square shows a table in the join.

The Join Diagram shows the inner join between the inventorytable and invoice database tables and a right join to the producttable database table.

When working with multiple joins, use this diagram to see the hierarchy of joins. Ensure that you are using the correct join types for your data. As you modify join types, the diagram updates to reflect the new join types.

Join Type Limitations

Some database vendors do not support all join types. The Database Explorer app enables the corresponding buttons in the Join tab for the supported join types in these databases:

  • SQLite supports only inner and left join types.

  • Microsoft Access® and MySQL® support only inner, left, and right join types.

See Also

Apps

Related Topics

External Websites