Loading patent…
AssigneeInternational Business Machines Corporation
InventorsJosephine M. Cheng, Ting Yu Leung, Mir Hamid Pirahesh
Estimating the term…
Method for specifying SQL "full outer joins" which enables the use of arbitrary join conditions in specifying the query. This is enabled by equating a full outer join with a left outer join unioned with a right outer join less the matched tuples from the right outer join. A number of embodiments further present improvements in execution speed. One such improvement utilizes as a second operand of the union query a novel operator, the "ANTI-JOIN". The anti-join is implemented as a right outer join coupled with an ISNULL predicate.
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having one or more columns, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processor implemented method for returning an answer set from the data in the tables responsive to a query to the database management system, the answer set requiring the matching of tuples from the first table and the second table, the method comprising the step of joining the tuples of the first and second tables using a full outer join with a join condition, the join condition being free of restrictions as to the type of join condition specified, the step of joining including unioning a left outer join with a right outer join less the matched tuples from the right outer join.
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processor implemented method for returning an answer set from the data in the tables responsive to a query to the database management system, the answer set requiring the matching of tuples from the first table and the second table, the method comprising the step of joining the tuples of the first and second tables using a full outer join with any join condition specifiable in the query language, the step of joining including unioning a left outer join with a right outer join less the matched tuples from the right outer join.
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processor implemented method for returning an answer set from the data in the tables responsive to a query to the database management system, the answer set requiring the matching of tuples from the first table and the second table, the method comprising the steps of:
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, both tables being tables other than base tables, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processor implemented method for returning an answer set from the data in the tables responsive to a query to the database management system, the answer set requiring the matching of tuples from the first table and the second table, the method comprising the steps of:
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processor implemented method for returning an answer set from the data in the tables responsive to a query to the database management system, the method comprising the steps of:
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processor implemented method for returning an answer set from the data in the tables responsive to a query to the database management system, the answer set comprising of the first table tuples that do not match the second table tuples, the method comprising the steps of:
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processor implemented method for returning an answer set from the data in the tables responsive to a query to the database management system, the answer set comprising tuples of the first table that do not match the tuples of the second table, the method comprising the steps of:
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processor implemented method for returning an answer set from the data in the tables responsive to a query to the database management system, the answer set requiring the matching of tuples from the first table and the second table, the method comprising the steps of:
A computer-implemented apparatus, with bus means, processor means, data storage means, input and output means and a relational database management system, for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having one or more columns, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, which apparatus returns an answer set from the data in the tables responsive to a query to the database management system, the apparatus comprising programmatic means for identifying the query as a full outer join query having an answer set requiring the matching of tuples from the first table and the second table, and programmatic means for joining the tuples of the first and second tables using a full outer join with a join condition, the join condition having the property of being free of restrictions as to the type of join condition specified, the means for joining comprising programmatic means for unioning a left outer join with a right outer join less the matched tuples from the right outer join.
A computer-implemented apparatus, with bus means, processor means, data storage means, input and output means and a relational database management system, for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, the apparatus for returning an answer set from the data in the tables responsive to a query to the database management system, the apparatus comprising programmatic means for identifying the query as a full outer join query having an answer set requiring the matching of tuples from the first table and the second table, and programmatic means for joining the tuples of the first and second tables using a full outer join with any join condition specifiable in the query language, the means for joining comprising means for unioning a left outer join with a right outer join less the matched tuples from the right outer join.
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processing apparatus ("apparatus") for returning an answer set from the data in the tables responsive to a query to the database management system, the answer set requiring the matching of tuples from the first table and the second table, the apparatus comprising:
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processing apparatus ("apparatus") for returning an answer set from the data in the tables responsive to a query to the database management system, the answer set requiring the matching of tuples from the first table and the second table, the apparatus comprising:
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processor for returning an answer set form the data in the tables responsive to a query to the database management system, the data processor comprising:
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processor for returning an answer set from the data in the tables responsive to a query to the database management system, the answer set comprising of the first table tuples that do not match the second table tuples, the data processor comprising:
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processor for returning an answer set from the data in the tables responsive to a query to the database management system, the answer set comprising of the first table tuples that do not match the second table tuples, the data processor comprising:
In a relational database management system utilizing a data processor for storing data in the form of at least a first table defining a first operand and a second table defining a second operand, the first and second tables comprised of tuples having at least one column, wherein the data contained in the relational database management system is retrievable by means of query language queries to the database management system, a data processor for returning an answer set from the data in the tables responsive to a query to the database management system, the answer set requiring the matching of tuples from the first table and the second table, the data processor comprising:
Dependencies are inferred from the claim text.
Drawings are temporarily unavailable. Open the PDF at USPTO
No related US applications are recorded in the indexed filing history.
Query rewriting; Transformation: of operators
Query rewriting; Transformation: optimisations to support specific applications; Extensibility of optimisers
Database or file accessing: access augmentation or optimizing
Database or file accessing: query processing, i.e. searching
Term ended 25 Jun 2017 (estimated)
USPTO file history isn’t available for this patent, so only indexed dates are shown.
1. Field of the Invention
This invention relates to query mechanisms for relational database management systems. More particularly, the present invention relates to a novel method of evaluating SQL "full outer joins", which methodology enables the use of arbitrary join conditions in specifying the query.
2. Description of the Related Art
For a more complete understanding of this invention, reference is now made to the following detailed description of the embodiments as illustrated in the accompanying drawing, wherein:
FIG. 1 shows a functional block diagram of a computer-implemented database processing system suitable for carrying out the present invention.
FIG. 2 is a flow diagram of the operating steps needed for interpreting and executing SQL statements in an interactive network environment.
While the principles of the present invention may be implemented using a number of RDBMS query languages, the preferred embodiments disclosed herein are programmatically implemented using the Structured Query Language, SQL. Reference in made to the SQL-92 standard "Database Language SQL" published by the ANSI as ANSI X3.135-1992 and published by the ISO as ISO/IEC 9075: 1992 for the official specification of the 1992 version of SQL. Each of these authorities is herewith incorporated by reference.
The System of the Invention
FIG. 1 shows a computer environment of the exemplary RDBMS system. In the exemplary computer environment, a computer system 102 at a node of the network accesses data storage devices, such as disk drives, in which are stored user and system tables 104 and log data tables 106. An operator of the computer system 102 uses a standard operator terminal interface 108, such as one of the interfaces known as IMS/DB/DC, CICS, TSO, OS/2, or some other similar interface, to transmit electrical signals to and from the computer system and monitor 109 that represent commands for performing various search and retrieval functions against the databases 104, 106. These search and retrieval functions are generally referred to as queries. In the preferred embodiment of the present invention, these queries conform to the SQL standard, and invoke functions performed by RDBMS software. In the preferred embodiment of the present invention, the RDBMS software comprises the "DB2" product offered by the International Business Machines (IBM) Corporation for the "MVS" or "OS/2" or "AIX" operating systems. Those skilled in the art will recognize, however, that the present invention has application to any RDBMS software that uses SQL.