How the Query Designer Represents Joins

See Also      Tasks

If tables are joined, the Query Designer represents the join graphically in the Diagram pane and by using SQL syntax in the SQL pane.

Diagram Pane

In the Diagram pane the Query Designer displays a join line between the data columns involved in the join. The Query Designer displays one join line for each join condition. For example, the following illustration shows a join line between two tables that are joined:

If tables are joined using more than one join condition, the Query Designer displays multiple join lines, as in the following example:

If the joined data columns are not displayed (for example, the input source window is minimized or the join involves an expression), the Query Designer places the join line at the title bar of the input source window.

The shape of the icon in the middle of the join line indicates how the input sources are joined. If the join clause uses an operator other than equal (=), the operator appears in the join line icon. The following table lists the icons that appear in the join line.

Join line icon Description
Inner join (created using an equal sign)
Inner join based on the "greater than" operator
Outer join in which all rows from the table represented on the left will be included, even if they do not have matches in the related table
Outer join in which all rows from the table represented on the right will be included, even if they do not have matches in the related table
Full outer join in which all rows from both tables will be included, even if they do not have matches in the related table.

Note   Some databases, such as Oracle, do not support full outer joins. For details, see Query Designer Considerations for Oracle Databases.


The symbols on the ends of the join line indicate the type of join. The following table lists the types of joins and the icons displayed on the ends of the join line.

Icon on ends of join line Type of join
One-to-one join
One-to-many join
Query Designer cannot determine the join type. This situation occurs most often when you have created a join manually.

SQL Pane

A join can be expressed in a number of ways in an SQL statement. The exact syntax depends on the database you are using and on how you have defined the join.

Syntax option for joining tables include:

Note   SQL Server databases support *= and =* syntax. For details, see Query Designer Considerations for SQL Server. Oracle databases support the { oj } syntax for outer joins. For details, see Query Designer Considerations for Oracle Databases.