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.
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.
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.
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.
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.
Opens the Join tab.
Adds a join for each matched column name, creates an SQL query with the added joins, and executes the SQL query.
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.
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.
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.
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.
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.
To add another join, select another table and column name combination in the left and right lists. Then, click Add Join again.
In the Edit section, click one of the join types (for example, ) to specify a different join type, if necessary.
To remove a join, select it in the list of joins in the Edit section, and click Remove Join.
To change the order of joins, remove existing joins and create joins in another order.
In the Close section, click Close Join to close the Join tab.
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.
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.
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.