Understanding the Terminology › Join

Join

Suppose you were required to provide a report listing the name, employee number, state, insurance code, and number of dependents for employees with dependents. The Personnel Table contains some of the information, arranged by employee number. The Benefits table contains the rest of the information, arranged by insurance code. Since the name column exists in each table, the query can use it to relate the rows in the tables to each other, whenever the same name appears in each table.

Joining the rows found in the first table to rows in other tables temporarily creates a new kind of table called a found set that contains the columns you need from each table, as in the following illustration. CA Dataquery retrieves the data for query output from the found set.

Simple Equijoin Example

          Personnel Table         Benefits Table
        ┌────┬───────┬────┐     ┌───┬───────┬─────┐
        │ NO │ NAME  │ ST │     │ C │ NAME  │ DEP │
        ├────│───────│────┤     ├───│───────│─────┤
        │  1 │ SMITH │ NY │     │ A │ SMITH │  2  │
        ├────│───────│────┤     ├───│───────│─────┤
        │  2 │ JONES │ OK │     │ B │ WHITE │  3  │
        ├────│───────│────┤     ├───│───────│─────┤
        │  3 │ GRAY  │ NY │     │ C │ GRAY  │  2  │
        └────┴───┬───┴────┘     └───┴──┬────┴─────┘
                 │                     │
                 └──────────┬──────────┘
                            │
                            │
                            │
                            │
                            ▼
                        Found Set
             ┌───────┬────┬────┬───┬─────┐
             │ NAME  │ NO │ ST │ C │ DEP │
             ├───────│────│────│───│─────┤
             │ SMITH │  1 │ NY │ A │  2  │
             ├───────│────│────│───│─────┤
             │ GRAY  │  3 │ NY │ C │  2  │
             └───────┴────┴────┴───┴─────┘

The previous illustration shows the traditional equijoin concept, meaning that tables will be joined when the common key or column contains equal values in each related table, and only joined rows will appear in the output.

At CA Dataquery Version 10.0, we provided the ability to perform outer joins, thus including specific unrelated rows in the found set. See the CA Dataquery Reference Guide for complete details.