A script-enabled browser is required for this page to function properly.

About Query Builder relationships

A relational database consists of a number of related tables. Each table is made up of columns (sometimes called fields) which are arranged vertically. Each column has a name, and contains a unique type of data.

Relationships, or joins, define how a column in one table relates to a column in another table. In many cases, the relationships you need are already defined in the database. Relationships also reduce data redundancy. Whenever there is a relationship between columns in different tables, you can use the tables together in a query.  Note: In general, Query Builder enables you to relate any columns that can be interpreted using the implicit conversion feature of the Oracle database.  For technical information on implicit conversions, refer to the Oracle9i Server SQL Language Reference Manual.

Related columns do not always share the same names. You can always relate columns that share the same datatype, and in some instances you can relate columns of different datatypes, if the relationship makes sense. For example, you can always relate a Number datatype column with a Character column, since a number can also be interpreted as a character. In fact, almost any column (except long text columns) can be related to Character columns.

Relationships can be user-defined (temporary) or established in the underlying data dictionary. Relationships in the data dictionary automatically appear when you include related tables (tables that have a primary/foreign key relationship). You do not have to do anything to use these relationships, since they are always valid. These can be created or modified using the Schema Builder.

User-defined relationships are valid only for the query in which you create them. Creating a user-defined relationship does not establish a corresponding permanent relationship in the underlying table. The main difference between the two is that in the case of a user-defined relationship, you must draw the relationship line; in a relationship stored in the database, Query Builder draws it for you.

A self-relationship relates a table to itself. Self-relationships have a limited, very specific use: to establish a parent-child link between two columns that are contained in the same table. For example, you could use a self-relationship to relate subsidiaries of a company to their parent company, or employees to their managers.

A self-relationship cannot be effective unless the columns in the table are counterparts of one another. In the sample database Employee table, for example, the Manager ID is the same as the Employee ID for that manager. So the self-relationship icon is displayed in the Manager ID column.

A composite relationship connects two or more columns in one table with columns in another table. For example, you might relate the columns Order ID and Item ID in the Items table with identically named columns in the Item List table.

Note: Reports Builder data links are not the same as Query Builder relationships. Data links are created between two or more queries, where Query Builder relationships (or joins) are created within a single query.

See also

Activating and deactivating a relationship (Query Builder)

 

Creating a composite relationship (Query Builder)

 

Creating a self-relationship (Query Builder)

 

Creating a simple relationship (Query Builder)

 

Creating an unmatched relationship (Query Builder)

 

Deleting a relationship (Query Builder)