Ingres 11.0 | Using JoinDefs in QBF

A JoinDef, or join definition, is a definition of a virtual table that conceptually joins together multiple tables in your database. A JoinDef does not exist as an actual table in the database, but temporarily links the information in different tables by their common columns. This enables you to work with the data in the tables simultaneously, as if it was contained in one table.

For example, in a relational database in which each table typically is devoted to information related to a single item of interest, you can have the following separate tables:

• Staff (employee's name, hourly rate, title, and manager)

• Tasks (employee's name, project identification, tasks, and the hours spent on each task)

If you wanted to find out what tasks have been assigned to employees who work for a certain manager, you would need to link the information in the Staff and Tasks tables by employee name.

When you use a JoinDef to query multiple tables, you gain access to the data in all of the columns in all of the joined tables. When you use a JoinDef to append new data or update existing data, QBF writes identical information into the common columns of the different tables in the JoinDef. For example, if a common Lastname column links two tables, the same last name is simultaneously written into both Lastname columns. QBF writes changes or additions you made in non‑common columns only to the table containing that column.

A JoinDef is similar to a view in that you can use either to display data contained in more than one table. However, whereas a JoinDef contains all of the columns from the joined tables, a view can contain only selected columns from the tables on which it is based. Also, you cannot use a view to append new data or modify existing data in the underlying tables, as you can with a JoinDef. You create JoinDefs with QBF, but you must use a query language to create a view. Once created, QBF lists a view as if it were a table, but always lists JoinDefs separately from tables. For additional information on views, see your query language reference guide.

You can use JoinDefs in QBF, RBF, and VIFRED. However, you cannot manipulate or access JoinDefs from within the Tables Utility, as you can a view.

The map in the following figure illustrates the route QBF takes when you choose JoinDefs from Ingres Menu or from the QBF menu:

Last modified date: 01/30/2023