In SQL join statements, tables are designated as either left or right. In Query Designer, data items are arranged vertically, starting from the top. When linking data items in Query Designer, the left table in SQL corresponds to the first or upper data item in Query Designer and the right table in SQL corresponds to the second or lower table in Query Designer.

When setting up a data item link between two data items, you always set up the link on the lower data item in Query Designer. To access the SQL advanced options, you open the properties of a data item, and then set the DataItemLinkType Property to SQL Advanced Options. The SQLJoinType property appears in the Properties window for the data item. The SQLJoinType property has the following values:

• Inner Join
  
• Left Outer Join
  
• Right Outer Join
  
• Full Outer Join
  
• Cross Join
  

This topic uses the following sample tables and query to demonstrate the SQL Advanced Options for data item links.

Salesperson/Purchaser Table

Sales Header Table

Sample Query

The following illustration shows the Query Designer for a query that links the Sale Header table with the Salesperson/Purchaser table on the Salesperson_code and Code columns, as specified by the DataItemLink property. In the illustration, the SQL Advanced Options property is set to Inner Join. For each of the data item links types discussed in the following sections, you change the SQL Advanced Options property to the appropriate type.

Inner Join creates a dataset by combining records from data item tables where a match is found between the columns that are linked by the DataItemLink property of the lower data item. Inner Join uses an "equal to" comparison operator to match rows from the lower data item table with rows from the upper data item table that is based on the values of the linked columns.

• Each pair of matching records is combined into a row in the dataset.
  
• Records from the upper and lower data item tables that do not have a matching column in the lower data item table are excluded from the resulting dataset.
  

The following illustration shows an Inner Join type between tables A and B. The shaded area indicates the records that are included in the resulting dataset.

Dataset Example

SQL SELECT Statement for Inner Join

To specify an inner join with an SQL statement, you can do either of the following:

• Use a WHERE clause.
  
• Use the INNER JOIN condition with an ON clause.
  

The following two examples show how to create an inner join on the Salesperson/Purchaser and Sales Header tables with SQL statements. These two statements result in the same dataset.

	Copy Code
SELECT "Salesperson/Purchaser".Name, "Sales Header"."No.", "Sales Header"."Sell-to Customer Name" FROM "Salesperson/Purchaser" INNER JOIN "Sales Header" ON "Salesperson/Purchaser".Code = "Sales Header"."Salesperson Code"

	Copy Code
SELECT "Salesperson/Purchaser".Name, "Sales Header"."No.", "Sales Header"."Sell-to Customer Name" FROM "Salesperson/Purchaser", "Sales Header" WHERE "Salesperson/Purchaser".Code = "Sales Header"."Salesperson Code"

A left outer join resembles the inner join except that the resulting dataset set contains every record from the upper data item table, even if a record does not have a matching value in the lower data item for columns that are linked by the DataItemLink property.

• For each record in the upper data item, a row is added in the dataset that combines columns from the upper and lower data item.
  
• When a record in the upper data item table has no matching record in the lower data item table, columns coming from the lower data item table have null values.
  

The following illustration shows a Left Outer Join type between tables A and B. The shaded area indicates the records that are included in the resulting dataset. In the sample query, the Salesperson/Purchaser table is considered the left table.

Dataset Example

SQL SELECT Statement for Left Outer Join

To specify a left outer join with an SQL statement, you use the LEFT OUTER JOIN condition.

The following example shows how to create a left outer join on the Salesperson/Purchaser and Sales Header tables by using a SQL statement.

	Copy Code
SELECT "Salesperson/Purchaser".Name, "Sales Header"."No.", "Sales Header"."Sell-to Customer Name" FROM "Salesperson/Purchaser" LEFT OUTER JOIN "Sales Header" ON "Salesperson/Purchaser".Code = "Sales Header"."Salesperson Code"

A Right Outer join resembles the inner join except that the resulting dataset set contains every record from the lower data item table, even if a record does not have a matching value in the upper data item for columns that are linked by the DataItemLink property.

• For each record in the lower data item, a row is added in the dataset that combines columns from the lower and upper data item tables.
  
• When a record in the lower data item table has no matching record in the upper data item table, columns coming from the upper data item table have null values.
  

The following illustration shows a Right Outer Join type between tables A and B. The shaded area indicates the records that are included in the resulting dataset.

Dataset Example

SQL SELECT Statement for Right Outer Join

To specify a left outer join with an SQL statement, you use the RIGHT OUTER JOIN condition.

The following example shows how to create a right outer join on the Salesperson/Purchaser and Sales Header tables by using a SQL statement.

	Copy Code
SELECT "Salesperson/Purchaser".Name, "Sales Header"."No.", "Sales Header"."Sell-to Customer Name" FROM "Salesperson/Purchaser" RIGHT OUTER JOIN "Sales Header" ON "Salesperson/Purchaser".Code = "Sales Header"."Salesperson Code"

A Full Outer join contains all the records from the upper data item table, and all records from the lower data item, including records that do not have a matching value for columns that are linked by the DataItemLink property.

• Each pair of records from the data items that have matching column values are combined into a row in the dataset.
  
• Records from the upper data item table that do have a matching column are included in a row, where the columns from lower data item table have null values.
  
• Records from the lower data item table that do have a matching column are included in a row, where the columns from upper data item table have null values.
  

The following illustration shows a Full Outer Join type between tables A and B. The shaded area indicates the records that are included in the resulting dataset.

Dataset Example

SQL SELECT Statement for Full Outer Join

To specify a left outer join with an SQL statement, you use the FULL OUTER JOIN condition.

The following example shows how to create a full outer join on the Salesperson/Purchaser and Sales Header tables by using a SQL statement.

	Copy Code
SELECT "Salesperson/Purchaser".Name, "Sales Header"."No.", "Sales Header"."Sell-to Customer Name" FROM "Salesperson/Purchaser" FULL OUTER JOIN "Sales Header" ON "Salesperson/Purchaser".Code = "Sales Header"."Salesperson Code"

A cross join contains rows that combine each row from the upper data item table with each row from a lower data item table. Cross joins are also called Cartesian products. A cross join does not apply any comparisons between columns of data items, so the DataItemLink property is left blank.

Dataset Example

SQL SELECT Statement for Cross Join

To specify a cross join with a SQL statement, you can do either of the following:

• Use the CROSS JOIN condition
  
• Create an implicit join, which has no join condition, without using a WHERE clause
  

The following examples shows how to create a cross join of the Salesperson/Purchaser and Sales Header tables by using a SQL statement.

	Copy Code
SELECT "Salesperson/Purchaser".Name, "Sales Header"."No.", "Sales Header"."Sell-to Customer Name" FROM "Salesperson/Purchaser" CROSS JOIN "Sales Header"

	Copy Code
SELECT "Salesperson/Purchaser".Name, "Sales Header"."No.", "Sales Header"."Sell-to Customer Name" FROM "Salesperson/Purchaser", "Sales Header"