Non-equijoin metadata

• US 8,914,352 B2
• Filed: 07/10/2007
• Issued: 12/16/2014
• Est. Priority Date: 07/10/2007
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Chaves, Michael, Belur, Kasturi, Shelton, Scott, Fu, Henry
• Primary Examiner(s): Yen, Syling
• Application Number:
  US11/775,462
• Publication Number:
  US 20090019017A1
• Time in Patent Office: 2,716 Days
• Field of Search: 707/3, 707/706, 707713-719, 707/921, 707/922, 707/936, 707/958, 707/781, 707999001-999004
• US Class Current: 707/714
• CPC Class Codes: G06F 16/24534 Query rewriting; Transformation

Abstract

The subject mater herein relates to database management and, more particularly, to non-equijoin metadata. Various embodiments described herein provide systems, methods, software, and data structures to facilitate non-equijoins of data. Some such embodiments include a data store to hold metadata describing relationships between database tables. The relationships between database tables may include one or more non-equijoins of tables in the form of one or more table-column pairs of a first table being non-equal to one or more table-column pairs of a second table. In some embodiments, the metadata may be used by a query generator to build query statements including one or more non-equijoin.

Claims

1. A system comprising:

a processor;

a data storage device;

a database, stored on the data storage device, to hold tables of data including columns and rows;

the database including a first table, a second table, and metadata, the metadata including a description of a non-equijoin of the first table and the second table including a non-equijoin operator and a correspondence corresponding to a column of the first table and a column of the second table, the non-equijoin operator being at least one of a greater-than operator, a greater-than-or-equal-to operator, a less-than operator, or a less-than-or-equal-to operator;

a query generator executed by the processor to;

receive a data request from a requestor, the data request including a request for the column of the first table in view of the column of the second table for which a non-equijoin is described in the metadata; and

build a query statement using the column of the first table, the column of the second table, and the non-equijoin operator as retrieved from the metadata in the database.

2. The system of claim 1, wherein the database includes a second column of the first table, a second column of the second table, and a second non-equijoin operator corresponding to the second column of the first table and the second column of the second table, wherein the column of the first table and the second column of the first table are paired in a first table-column pair, the column of the second table and the second column of the second table are paired in a second table-column pair, and wherein a non-equijoin pairing between the first table-column pair and the second table column pair are described in two forms, the first form consisting essentially of:

the column of the first table+the first non-equijoin operator+the column of the second table; and

the second column of the first table+the second non-equijoin operator+the second column of the second table; and

the second form consisting essentially of;

the column of the second table+the opposite of the first non-equijoin operator+the column of the first table; and

the second column of the second table+the opposite of the second non-equijoin operator+the second column of the first table.

3. The system of claim 2, wherein the column of the first table and the second column of the first table are the same.

4. The system of claim 1, wherein the query generator is further executed by the processor to return the query statement to the requestor.

5. The system of claim 1, wherein the query generator, when building a query, determines an order for the table-column pairs of a non-equijoin and reverses non-equijoin operators if the order of table-column pairs is reversed.

6. A method of providing non-equijoin data to a query generator, the method comprising:

storing, in a database, a first table, a second table, and metadata, the metadata including a description of a non-equijoin of the first table and the second table including a non-equijoin operator and a correspondence corresponding to a column of the first table and a column of the second table, the non-equijoin operator being at least one a greater-than operator, a greater-than-or-equal-to operator, a less-than operator, or a less-than-or-equal-to operator;

receiving a data request from a requestor including a request for making a join between the column of the first table and the column of the second table;

retrieving the non-equijoin operator from the metadata in the database; and

building a query statement using the column of the first table, the column of the second table, and the non-equijoin operator as retrieved from the database.

7. The method of claim 6, wherein the database includes a second column of the first table, a second column of the second table, and a second non-equijoin operator corresponding to the second column of the first table and the second column of the second table, wherein the column of the first table and the second column of the first table are paired in a first table-column pair, the column of the second table and the second column of the second table are paired in a second table-column pair, and wherein a non-equijoin pairing between the first table-column pair and the second table column pair are described in two forms, the first form consisting essentially of:

the column of the first table+the first non-equijoin operator+the column of the second table; and

the second column of the first table+the second non-equijoin operator+the second column of the second table; and

the second form consisting essentially of;

the column of the second table+the opposite of the first non-equijoin operator+the column of the first table; and

the second column of the second table+the opposite of the second non-equijoin operator+the second column of the first table.

8. The method of claim 7, wherein the column of the first table and the second column of the first table are the same.

9. The method of claim 7, further comprising:

providing the query statement to the requestor for execution.

10. The method of claim 7, wherein building the query statement includes:

determining an order of the table-column pairs of a non-equijoin; and

reversing the non-equijoin operator if the determined order of table-column pairs is reversed from the way the correspondence between the column of the first table, the column of the second table, and the non-equijoin operator is stored in the database.