Joins

Go Up to Using Query Builder

Joins let you distill the information in your database to a usable form. Query Builder lets you create, manipulate, and edit work with joins without requiring knowledge of the underlying SQL code. Query Builder lets you create any type of join for SELECT and CREATE VIEW Statements. You can create self joins for UPDATE or DELETE Statements. You cannot create joins for INSERT Statements.

Query Builder includes four types of joins. The table below describes joins and their availability in Query Builder:

Join	Statement Availability	Description
Inner Joins	SELECT, CREATE VIEW, DELETE, UPDATE	Returns data from the joined tables that match the query’s join criteria and set a relation between tables or views. Inner joins return results where the join condition is true.
Left Outer Joins	SELECT, CREATE VIEW	Returns all data from the primary table and data from the joined tables that match the query’s join criteria and set a join relation operator from a column in a primary table or view to a column in a secondary table or view.
Right Outer Joins	SELECT, CREATE VIEW	Returns all data from the primary table and data from the joined tables that match the query’s join criteria and set a join relation operator from a column in a secondary table or view to a column in a primary table or view.
Self Joins	SELECT, CREATE VIEW	Set a relation between columns in the same table.

In the Query Builder SQL Diagram Pane, you can create, edit, and delete joins. You can edit joins in the Join dialog box. For details, see Editing Joins.

You can set Query Builder options, in the Options editor, to automatically create joins. For details, see Query Builder Options.

Joins are the way you can filter data in relational databases. Query Builder lets you change the types of joins between tables, views and columns. It is important that you have some knowledge of the data in your tables, and the datatypes for each column. This information helps you frame a better query, and filter your data for maximum effect.

Inner Joins

Inner joins are the most common types of joins for SELECT statements. An inner join returns information from two tables where the relation between two target columns is true for both columns.

The join operand determines the relation results, for example, if the join operand is equals, then identical data, in two columns, is the only result. If the join operand is not equals, Query Builder only returns data that is different between two columns.

For example, if you have an inner join matching territory numbers between the table dbo.Managers and dbo.Clients, running the query returns all Managers and Clients with matching territory numbers:

Query Builder displays the following results from this query with an inner join

Note: Query Builder displays results of columns in the order of selection. You can reorder columns by deselecting and selecting in the SQL Diagram Pane, the Selected Columns Properties dialog box, or the SQL Statement Tree.

Left Outer Joins

Left outer joins bring back a different data set than Inner Joins. Left outer joins retrieve all the data in columns selected from the primary table, and only matching data from the joined or secondary table.

For example, in the same pair of tables, a left inner join from dbo.Managers to dbo.Clients, where the columns Current Territory and Territory are joined, displays different results.

Note: There is one additional manager who does not have a client, but because a left outer join includes all data from selected columns in the primary table, the last entry in the illustration is displayed.

Right Outer Joins

Right outer joins return opposite results from Left Outer Joins. In a right outer join, you are asking for all the information in the secondary table’s column, and the join operator’s matching information from the primary table.

For example, in the same set of data we used in the left outer join example, a right outer join returns all clients from dbo.Client, and only managers who match territory numbers, in the joined column.

Note: The managers are the same as the first, inner join, but a right outer join returns the additional clients without matching managers.

Self Joins

A self join is a join within a single table. Query Builder lets you return specific information from a single table using a self join.

For example, in our example table, there is a column for the number of clients and another column with the goal client total for a territory.

A self join can ascertain which managers are reaching their quota. Notice that the join relation operator in the example is greater than or equal to, which shows managers exceeding quota as well.

Adding and Deleting a Join in the SQL Diagram Pane

Query Builder lets you add and delete joins. This method adds a WHERE clause in your query. You can join different tables and or views in a SELECT or CREATE VIEW statement.

Adding a Join

To add a Join, do the following:

1. In the SQL Diagram Pane, drag the target column to the second column.

   Query Builder displays both a line joining the two columns in the SQL Diagram Pane and the corresponding SQL code in the SQL Statement Pane.

Removing a Join

Query Builder lets you remove joins from your query. Query Builder automatically deletes joins from the query in the SQL Statement Pane, when you remove them from the SQL Diagram Pane.

To remove a join, do the following:

1. Click the target join, and then on the Query Builder tool bar, click Delete.

OR

Right-click the target join, and then click Delete.

Query Builder deletes the Join.

Editing Joins

Query Builder lets you edit joins with the Join editor. You can edit join parameters in a SELECT, UPDATE, DELETE, and CREATE VIEW Statement.

The table below describes the options in the Join dialog box:

Option	Description
From Table Column	The primary column in the join.
To Table Column	The secondary column in the join.
Select the join relation operator	Click the target join operator. If it is not equals, the operator displays on the join in the SQL Diagram Pane.
Join Type: Inner	Click to make the join an inner join. Aggregates are only available for inner joins.
Join Type: Left Outer	Click to make the join a left outer join.
Join Type: Right Outer	Click to make the join a right outer join.

Completing the Join Dialog Box

1. In the SQL Diagram Pane, right-click the target join, and then click Properties.

OR

In the SQL Diagram Pane, double-click the target join.

OR

On the SQL Statement Tree, expand the Where and And nodes, and then double-click the target join.

Query Builder opens the Join dialog box.

Note: For IBM DB2 for Linux, Unix, and Windows and IBM DB2 for z/OS and OS/390 servers, there is an additional join object in the SQL Statement Tree. The Join On node displays join relations between columns in IBM DB2 for Linux, Unix, and Windows and IBM DB2 for z/OS and OS/390 tables and views.

Changing a Join Color

Query Builder lets you change the color at a join in the SQL Diagram Pane. Complex statements using many tables and multiple joins can be easier to view if joins have different colors.

To change the color of a join, do the following:

1. Right-click the target join, and then click Color.

   Query Builder opens the Color dialog box.

2. In the Basic colors grid, click a target color

   OR

   Click Define Custom Colors, then create a custom color.

   Note: Query Builder lets you save custom colors for the current color. Click Add to Custom Color to have the option of using that color for your queries.

3. Click OK.