Create joins

Creating joins

A join is a relational operation in a SELECT statement that lets you retrieve data from two or more tables based on matching column values. The data in the tables is linked into a single result.

SQL support in the Create A New SQL Statement wizard and the SQL Builder is dependent on the level of support provided by your database vendor.

Joins support:
DB2: inner joins, left outer joins, right outer joins, and full outer joins
Oracle: inner joins, left outer join and right outer joins. The syntax for left and right outer joins is consistent with the Oracle "+" syntax in the WHERE clause.
Sybase and Cloudscape: inner joins, left outer join and right outer joins.

The following table lists the join operators:

Join Operator Definition
Inner join Each row of the left table is combined with each row of the right table, keeping only the rows where the join condition is true. The result table may be missing rows from either or both of the joined tables. Outer joins include the inner join and preserve these missing rows. This is the default operator for a new join in the SQL Builder.
Left outer join Includes rows from the left table that were missing from the inner join.
Right outer join Includes rows from the right table that were missing from the inner join.
Full outer join Includes rows from both the left and right tables that were missing from the inner join.

To create a join in the SQL Builder:

Switch to the Data perspective.
Open your SELECT statement in the SQL Builder.
Add two or more tables to your SELECT statement.
In the Tables pane, drag the pointer from a column in one table (left table) to the column that you want to create the join to in another table (right table).
To change the join type from the default inner join, right-click the connection line and then click Specify Join Type on the pop-up menu. For information about different join types, select one and then press F1.

To create a join in the Create A New SQL Statement wizard:

Click File > New > Other.
In the New window, expand the Data folder, click SQL Statement, and then click Next.
Fill in the fields on the wizard pages, as necessary. For more information about individual fields, select them and press F1.
On the Joins page, select the source column in one table (left table) and drag the cursor to the column you want to create the join to in another table (right table).
To change the join type from the default inner join, right-click the connection line and click Specify Join Type. For more information about different join types, select one and press F1.
Modifying a join
To move the join from one column to another, drag the end of the connection line to the new column.
Removing a join
To remove the join, right-click the join connection line and then click Remove Join on the pop-up menu.

Parent topic
SELECT statement retrieves data from a database and returns it in the form of a table. It can be embedded in an application or used interactively.">Creating a SELECT statement

Join Operator	Definition
Inner join	Each row of the left table is combined with each row of the right table, keeping only the rows where the join condition is true. The result table may be missing rows from either or both of the joined tables. Outer joins include the inner join and preserve these missing rows. This is the default operator for a new join in the SQL Builder.
Left outer join	Includes rows from the left table that were missing from the inner join.
Right outer join	Includes rows from the right table that were missing from the inner join.
Full outer join	Includes rows from both the left and right tables that were missing from the inner join.