Documentation Visual SQL

Merge Queries

If you joined before January 6, 2020, you might be using our old Data Explorer interface. Check out our Data Explorer page for more relevant content.

What if your chart needs data from multiple data sources or from tables with no foreign key relations? You can add another Query to your Pipeline, which lets you pull data from a different data source or unrelated table. Once you’ve built and run your second Query, you’ll need to merge the Queries together with a Join, blending them into a single result set for your chart. Chartio makes it fast and easy to merge the result sets of disparate data sources, allowing for powerful post-processing and calculations. Give it a try and add as many Queries as you’d like to your Pipeline!

Merging Queries example

1. Add another Query

After executing your initial Query, you can add a new Query or clone an existing one to your Pipeline by clicking Add Query above the Result Table. Keep in mind, you can add the new Query anywhere in the Pipeline—just first select the Action you’d like the new Query to merge with before you click “Add Query”.

Tip! Added the Query to the wrong place in the Pipeline? Use Undo to go back to the previous state—or just remove the Query, that works too.

Depending on the type of join you have in mind, you may want to consider having a related (or common) column to join your Queries on. A common column refers to a column in each result set containing similar values. For example, if two queries share a date column, merging the queries will combine records with matching dates, resulting in a more insightful output.

Keyboard shortcuts! To add multiple columns at a time without the schema browser closing between each selection, hold Command (for Mac) or Ctrl (for Windows) then click the columns you wish to add.

2. Transform your Query

After you’ve built and executed (i.e., clicked Run Query) your new Query, you can transform its result set with other Actions before it’s joined with the other result set.

3. View your merge results

When you add a new Query to the Pipeline, a Join Action is added to your Pipeline, following your new Query (and any transformations you’ve applied to it). Your new Query’s result set is automatically merged with the result set of the immediately preceding Action (Query or otherwise). To see the results of the merge, click the Join Action.

By default, the result sets merge by an Outer Join on the first columns on the left, but you can change the join type and the number of columns to join on by editing the Join Action in the Pipeline.

Note: Remember you can use a Reorder Action to move columns in your result set.

4. Choose your join type

As mentioned above, you can change the join type of your merge. When you merge, the left table’s data is joined with the right table’s data. Think of the top result set as the left table and the nested result set (i.e., the new Query’s result set) as the right table. This may help you choose your join type.

Each join type may have other fields you can change to customize your merge. For more details on these fields, check out our Join Action documentation.

Choose the join type of your merge by editing the Join Action

JOIN types

If you’re not sure which join to use, here are brief descriptions and illustrations for each available join type:

JOIN Type	Visual	Description
OUTER JOIN		Combines the columns from all result sets on one or more common columns when possible, and includes all data from both result sets.
INNER JOIN		Combines the columns on a common column (the first N columns) when possible, and only includes data for the columns that share the same values in the common N column(s).
LEFT JOIN		Combines the columns on a common column (the first N columns) when possible, returning all rows from the first result set with the matching rows in the second result set. The result is NULL in the second result set when there is no match.
UNION		A Union merge will stack the result sets on top of each other without grouping or combining the data. Unions can be used to generate lists of data to be printed or viewed in table format. To remove duplicate rows, check the Distinct checkbox.
CROSS JOIN		The result of the Cross Join will be a table with all possible combinations of your result sets together. This can result in enormous tables and should be used with caution. Cross Joins will likely only be used when your result sets are returning single values.

If you still need some help understanding join types, check out this awesome article from our Data School that gives visual explanations of SQL join types.