Edit Join Dialog

Use the Edit Join dialog to modify properties of a table join created in the SQL Designer (the Designer is used to create a custom query for a database checkpoint).

The join means the query will retrieve only those fields and rows from a table that have matching values in another table.

The dialog is called when you right-click the join line in the Designer and choose Edit from the context menu.

The two Table boxes display the names and aliases of the joined tables (if a table has an alias, the table’s name is enclosed in parentheses). The Column boxes show the columns which are used for the join. All of these values (table names and column names) are read-only. However, you can change the join expression by selecting the desired condition from the drop-down list that resides between the column names.

Using the two Add all rows check boxes you can specify how the tables will be joined and which query the Designer will generate. The join means the query will analyze the table rows and include only those rows and data that have matching values in another table. The following table explains how the check boxes work. We assume that the joined tables are named Table1 and Table2:

Condition	Description
Add all rows of Table1 is clear Add all rows of Table2 is clear	The query will include only those rows of Table1 that have matching rows in Table2. The same is for Table2: the query will include only those rows of Table2 that have matching rows in Table1. This condition corresponds to the INNER JOIN clause of the SQL SELECT comment. The Designer will generate this clause for your query. For instance: Example ExampleExample SQLCopy Code SELECT   Table1.FieldA,   Table1.FieldB,   Table2.FieldX,   Table2.FieldY, FROM   Table1   INNER JOIN     Table2   ON     Table1.ID = Table2.ID
Add all rows of Table1 is selected Add all rows of Table2 is clear	The query will include all rows of Table1 regardless of whether they have matching values in Table2. As for Table2, the query will include only those rows which have the appropriate rows in Table1. In other words, when this condition is active, Table1 acts as a master table and Table2 is considered to be a detail table. This condition corresponds to the LEFT OUTER JOIN clause of the SQL SELECT command. The Designer will generate this clause for your query, for instance: Example ExampleExample SQLCopy Code SELECT   Table1.FieldA,   Table1.FieldB,   Table2.FieldX,   Table2.FieldY, FROM   Table1   LEFT OUTER JOIN     Table2   ON     Table1.ID = Table2.ID
Add all rows of Table1 is clear Add all rows of Table2 is selected	This condition is contrary to the previous condition. The query will include all rows of Table2 and only those rows of Table1, which have the matching rows in Table2. This condition corresponds to the RIGHT OUTER JOIN clause of the SQL SELECT command. The Designer will generate this clause for your query, for instance: Example ExampleExample SQLCopy Code SELECT   Table1.FieldA,   Table1.FieldB,   Table2.FieldX,   Table2.FieldY, FROM   Table1   RIGHT OUTER JOIN     Table2   ON     Table1.ID = Table2.ID
Add all rows of Table1 is selected Add all rows of Table2 is selected	This condition will include all the rows from both tables. If a table does not contain the matching rows in another table, the query fields that correspond to columns of the other table will contain null values. This condition corresponds to the FULL OUTER JOIN clause of the SQL SELECT comment. The Designer will generate this clause for your query. For instance: Example ExampleExample SQLCopy Code SELECT   Table1.FieldA,   Table1.FieldB,   Table2.FieldX,   Table2.FieldY, FROM   Table1   FULL OUTER JOIN     Table2   ON     Table1.ID = Table2.ID

To save the changes you made to the join properties in the dialog, click OK. To close the dialog without saving any changes, click Cancel.

See Also

SQL Designer