Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2000-07-10
2003-10-28
Shah, Sanjiv (Department: 2172)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000
Reexamination Certificate
active
06640221
ABSTRACT:
BACKGROUND OF THE INVENTION
1. Technical Field
The present invention is directed to the field of database queries. More specifically, the invention provides a system and method for configuring, sequencing and viewing joins in a database query.
2. Description of the Related Art
In today's information economy, companies increasingly rely on information systems to remain competitive. Foremost among these information systems are database management systems that utilize relational databases. These systems are generally referred to as relational database management systems. Relational databases organize data records into a multitude of interconnected tables. Each table consists of rows and columns of data from the data records stored in the database. The rows of data are also known as tuples.
A relational database management system typically includes software for searching and retrieving data records from the relational database. The most common means for accomplishing this search and retrieval task is the Structured Query Language (SQL). SQL provides a software framework for logically structuring complex conditional expressions (i.e., relationships) for querying a database. SQL includes many different types of logical constructs, including the WHERE clause, the SELECT statement, the HAVING clause, and the ON clause. A WHERE clause is typically structured as follows: WHERE (variable
1
<operator> condition
1
) link (variable
2
<operator> condition
2
). The WHERE clause returns data records from the database that meet the two conditional expressions (variable
1
<operator> condition
2
) link (variable
2
<operator> condition
2
), depending on the type of link. Two common forms of link operators for conditional expressions are the “AND” link and the “OR” link.
The SQL ON clause typically forms part of the SQL SELECT statement, and it is used to define a set of relationships (conditional expressions) that are associated with joining two or more tables. In SQL syntax, a join may be defined as a relational operation that allows a program to retrieve data from two or more tables based on matching column values.
IBM Dictionary of Computing
, 10th Edition, p. 365. The SQL join operation combines the data rows (or tuples) from two or more tables into a result set according to the relationships expressed in the ON clause, and also according to the type of join. The result set is a data table that includes the merged data rows from the two or more tables operated on by the join that meet the relationships set in the ON clause.
In an SQL statement that includes more than one join operation, there are two types of result sets, a final result set and one or more intermediate result sets. The final result set is the final table that is generated from the tables being joined after all the join operations are executed. The intermediate result set is the intermediate table that is generated from just two of the tables (or one table and another intermediate result set) being joined in one of the join operations. As discussed below, certain types of joins, such as the outer join, internally produce intermediate result sets.
A generic SQL statement for generating a result set from a join operation may be expressed as follows:
SELECT [Cols] from [Table
1
] join type [Table
2
] ON {CONDITION =<relationship
1
> operator <relationship
2
> operator <relationship N> }.
Here, Table 1 and Table 2 are the two tables operated on by the join. There are several different types of join that can be used to merge data from tables. The most common join types include the inner join, the left outer join, the right outer join, and the full outer join.
The condition of the ON clause includes one or more relationships coupled together by one or more operators. For example, relationship
1
could be “Table
1
.column
1
=Table
2
.column
1
” and relationship
2
could be “Table
1
.column
2
=Table
2
.column
2
.” The operator that couples relationship
1
to relationship
2
could be the AND operator, or it could be some other type of logical operator. In this example, the condition of the join is met when the data entry in the first column of the first table is equal to the data entry in the first column of the second table and where the data entry in the second column of the first table is equal to the data entry in the second column of the second table.
An inner join of two tables returns only the rows in each table that meet the condition in the ON clause—i.e., the matching rows. In the left, right and full outer joins, however, rows in one of the tables may be returned into the result set even though there are no matching rows in the other table (or intermediate result set). In a left outer join the left table is preserved, meaning that all of the matching and non-matching rows from the left table are returned into the result set. For the right table in the left outer join, only the matching rows are returned, similar to the inner join. Likewise, in a right outer join the right table is preserved, so that all of the matching and non-matching rows from the right table are returned into the result set and only the matching rows from the left table are returned. The syntax left versus right in a join operation is determined by the placement of the table in the SQL statement. In the example above, Table
1
is the left table and Table
2
is the right table. A full outer join combines the rows that are returned from both a left and a right outer join.
Graphical interfaces for visually depicting tables and their relationships are known in this field. These graphical interfaces generally depict the tables as boxes. The boxes may include the title of the table and a listing of the various column variables. Relationships between the tables are then depicted as lines that connect a column variable in one table to a column variable in another table. Numerous relationships may be depicted between the tables, and thus there is often more than one line connecting the tables together. In order to set the join type between two tables, a user typically selects one of the column relationships (i.e., picks a line that connects the tables) and then sets the joint type based on the selected relationship.
In a query tool that only permits inner joins, these known interfaces provide some utility. In a tool that enables more complex join operations, however, such as the various forms of outer joins discussed above, these known interfaces suffer from two significant problems. The first problem relates to the ambiguity created when there is more than one relationship between the tables and the user selects one of the relationships and sets the join type to an outer join. The problem here is how to configure the other non-selected relationship(s) that connect the two tables. One way to configure these other relationships is to assume that they represent AND conditions to the relationship that was set to an outer join. But another way is to configure these other relationships as separate joins. This outer join ambiguity problem inherently results from setting the join type based upon one of the column relationships.
The second problem with these known interfaces and tools relates to properly sequencing the order in which the tables are joined. Like the first problem, this second problem does not typically apply to a tool that only permits inner joins. For the more complex tool, however, which permits various forms of outer joins, the issue of proper table sequence is important. The final result set of a query may change depending upon the order (sequence) of the tables in the generated SQL when there are one or more outer joins. This occurs because the outer join operation internally produces an intermediate result set, which is then joined with subsequent tables in the join. Thus, changing the sequencing of the tables can change the query results when outer joins are involved. Known query tools and interfaces do not address this
Boozer John Forrest
Giles Steven
Levine Fred
Day Jones
SAS Institute Inc.
Shah Sanjiv
LandOfFree
System and method for configuring, sequencing and viewing... does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with System and method for configuring, sequencing and viewing..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and System and method for configuring, sequencing and viewing... will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3153665