• Products
  • Documentation
  • Resources

Join types

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

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

You can also select whether or not to include NULL match rows.

Two intersecting circles where all areas are filled in.

Inner join

Combines the columns on a common column (one or more leftmost columns) when possible, and only includes data for the columns that share the same values in the common columns.

Two intersecting circles where only the intersecting area is filled in.

Left join

Combines the columns on a common column (one or more leftmost 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.

Two intersecting circles where only the left circle and the intersecting area are filled in.

Union

Stacks the result sets on top of each other without grouping or combining the data. Use unions to generate tabular lists of data for printing or viewing.

To remove duplicate rows, select the Distinct checkbox.

If you select to include query names, this adds an extra column titled Layer to your result set. It labels each row with name of the query that it came from.

Two filled in circles with a plus sign between them.

Cross join

Results in a table with all possible combinations of your result sets together. This can result in enormous tables, so use a cross join with caution. For example, only use it when your result sets return single values.

Two single-column three-row tables where the first rows are colored in and arrows point from the left to the right table.

Still need help?

The Atlassian Community is here for you.