Efficient implementation of full outer join and anti-join5963933Abstract 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. Claims We claim: Description BACKGROUND OF THE INVENTION
______________________________________
SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM T1 q1 LEFT OUTER JOIN T2 q2
ON (q1.c1>=q2.c2)
______________________________________
This query returns all the matching pairs of T1 and T2 tuples based on the join condition "q1.c1>=q2.c2", just as in a regular join. The query also returns rows of T1 which do not match any T2 rows based on the join condition. In this example, T1 is called the tuple-preserving operand, while T2 is called the null-producing operand, because T1 tuples will be preserved when there is no match in T2. The Right Outer Join The right outer join is the same as left outer join, except that the join operands, i.e., the participating tables, are commuted. Thus, the right outer join includes rows from the right table that have no matching values in the left table. These rows are again given null values for any missing data elements. Accordingly, the following two outer joins are equivalent:
______________________________________
SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM T1 q1 LEFT OUTER JOIN T2 q2
ON (q1.c1>=q2.c2)
SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM T2 q2 RIGHT OUTER JOIN T1 q1
ON (q1.c1>=q2.c2)
______________________________________
The Full Outer Join A full outer join includes both those rows from the left table that have no matching values in the right table, as well as those rows from the right table that have no matching values in the left table. In either case, rows lacking matching values in the opposite table are given null values for the missing data. Consider the following query:
______________________________________
SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM T1 q1 FULL OUTER JOIN T2 q2
ON (q1.c1>=q2.c2)
______________________________________
This query returns all the matching pairs of T1 and T2 tuples based on the join condition "q1.c1>=q2.c2". The query also returns rows of T1 which do not match any T2 rows based on the join condition as well as rows of T2 which do not match any T1 rows. Note that a full outer join is not equivalent to a union of a left outer join and a right outer join. It will therefore be appreciated that the full outer join defined above is not identical to the following query:
______________________________________
SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM T1 q1 LEFT OUTER JOIN T2 q2
ON (q1.c1>=q2.c2)
UNION ALL
SELECT q3.c1, q3.c2, q4.c1, q4.c2
FROM T1 q3 RIGHT OUTER JOIN T2 q4
ON (q3.c1>=q4.c2)
______________________________________
This is due to the fact that the regular join portion of the full outer join is performed twice: once by the left outer join and again by the right outer join, thereby leading to an incorrect result. Many database management systems support left and right outer joins. Some systems further support full outer joins, but they impose the strict limitation that only the conjunction of equality predicates is allowed as the join condition. This limitation is due to the fact that these systems implement the full outer join using a modified merge join algorithm which, prior to the making of the present invention, has inherently been restricted to the use of equality predicates. Efforts at outer join optimization have been made by others. Some of the inventors of the present invention teach outer join optimization in U.S. Pat. No. 5,557,791. Further, P. Goel and B. Iyer address this matter in "SQL Query Optimization: Reordering for a General Class of Queries.", published in the Proceedings of the ACM SIGMOD '96 International Conference on Management of Data, Montreal, Canada, pp 47-56, (1996). Finally, G. Bhargava, P. Goel and B. Iyer present another methodology in "Hypergraph based reorderings of outer join queries with complex predicates.", Proceedings of the ACM SIGMOD '95 International Conference on Management of Data, San Jose, Calif., pages 304-315, (1995). In general, each of these references regarding outer join implementation shares common theme: the strategies taught therein require special runtime operators, which somehow keep track of which tuples must be preserved. In the prior art this has typically been done by creating and maintaining some list of tuple identifiers, or TIDs, for those tuples which must be preserved. When the outer join condition involves only equality predicates in the conjunctive normal form, it is possible to implement the full outer join via a modified merge join algorithm without keeping lists of preserved tuple identifiers. However, a modified merge join, according the prior art, cannot be used for arbitrary join conditions, including but not necessarily limited to the inequality predicate and disjunction, as specified in IPSO-ANSI92. Moreover, the creation and maintenance of TID lists introduces an unwanted and now unnecessary element of computational overhead, thereby inducing system performance problems in any system which relies on such lists. Join conditions are well defined in IPSO-ANSI92. Typically, join conditions defining the relationship between tuples are logically selectable from the following set of predicates: is null; like; equals (=); does not equal (.noteq.) is greater than (>); is less than (<); is greater than or equal to (.gtoreq.); and is less than or equal to (.ltoreq.). A plurality of these conjunction predicates can also be logically joined by the logical operators AND, OR, and NOT. Accordingly, as used herein, the term "arbitrary join conditions" defines any of these join conditions, or a plurality of join conditions joined by one of the defined logical operators. This precisely defined term defines over prior art full outer join methodologies, which support only the equality predicate. Finally, the computation of large queries implementing a full outer join can be resource intensive. This problem is often exacerbated by increasingly complex query structures where the union of a plurality of outer joins repeatedly returns a number of instances of a matching pair. Any solution to the problem of using a modified merge join to implement a full outer join and which further enables the use of arbitrary join conditions should, to the greatest extent possible, minimize the impact of the query on system resources by only returning the first instance of a matching pair. Accordingly, there is a clearly-felt need in the art for a methodology which enables the use of a modified merge join to implement a full outer join which enables the use of arbitrary join conditions other than the equality predicate. There is a further need that this query methodology return only a first instance of a matching pair in response to a join condition, thereby enabling the methodology to be resource efficient. These unresolved problems are clearly felt in the art and are solved by this invention in the novel manner described below. SUMMARY OF THE INVENTION This invention devises, for the first time, a mechanism which enables the implementation of the full outer join with any join condition, including but not necessarily limited to the inequality predicate and disjunction. This is enabled in one embodiment of the present invention by equating, in a novel manner, a full outer join with a left outer join unioned with a right outer join less the matched tuples from the right outer join. To implement this embodiment, a full outer join query is programmatically rewritten as a union of a left outer join and a select query with a "not-exists" subquery. Indeed, the present invention provides, for the first time, a methodology for properly unioning a left outer join and a right outer join whereby the result is a properly formed full outer join producing a correct answer set. It is recognized that transforming a full outer join into a union query with a not-exists subquery may present suboptimal performance in some applications. This may be due to the fact that subqueries tend to be slow to execute, especially in massively parallel processing (MPP) shared-nothing environments. An improved embodiment therefore programmatically transforms the full join query into a union query of a left outer join and a right outer join but for the right outer join, all the matched rows are filtered out so that a correct answer set results. This embodiment requires the use of at least one non-nullable column. Where an outer join operand does not have any non-nullable column, for instance as when the operand is a derived table, a further improved embodiment adds a column of constants to achieve the same effect as the previous embodiment. For example, suppose both tables DT1 and DT2 are not base tables: i.e., their row identifiers are not readily available, and all columns are nullable. By adding a column, or key, of constant value 1, or indeed of any other constant, in the operand of the right outer join, it can be assured that the column value is null for the preserved tuples of DT2. Hence, it is always possible to select those DT2 rows that do not have any matching rows in DT1. Given that the principles of the present invention implement a full outer join as a union of a left and a right outer join, a further optimization is accomplished by the final embodiment of the present invention by recognizing that all the matched rows in the right outer join are filtered out. This embodiment is implemented by using as a second operand of the union query a novel operator, the "ANTI-JOIN". The anti-join can be implemented as a right outer join coupled with an ISNULL predicate, or equivalently, it can be implemented directly using a new run time operator. Since computing all matched rows which will be eventually filtered out is computationally inefficient, the use of the anti-join presents significant savings in efficiency of implementation. It is emphasized that the principles of the present invention achieve the several features and advantages hereof without any restriction on the join condition for the full outer join. This is true because there is no restriction on the join condition for left and right outer joins. An additional advantage of the present invention is that the features and advantages of the several embodiments taught herein are obtained without a requirement for any new runtime operators to implement the full outer joins. The foregoing, together with other objects, features and advantages of this invention can be better appreciated with reference to the following specification, claims and accompanying drawing. BRIEF DESCRIPTION OF THE DRAWING 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. FIG. 3 is a flow diagram of the operating steps needed for interpreting and executing SQL statements embedded in source code for batch operation. FIG. 4 is a flow diagram of a first preferred embodiment of the present invention. FIG. 5 is a flow diagram of a second preferred embodiment of the present invention implementing a first filtering methodology. FIG. 6 is a flow diagram of third preferred embodiment of the present invention implementing a second filtering methodology. FIG. 7 is a flow diagram of fourth preferred embodiment of the present invention implementing an anti-join having an early out property. DESCRIPTION OF THE PREFERRED EMBODIMENTS 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. As illustrated in FIG. 1, the DB2 product architecture for the MVS operating system includes three major components: the Resource Lock Manager ("RLM") 110, the System Services module 112, and the Database Services module 114. The RLM handles locking services, because DB2 treats data as a shared resource, thereby allowing any number of users to access the same data simultaneously, and thus concurrency control is required to isolate users and to maintain data integrity. The Systems Services module 112 controls the overall DB2 execution environment, including managing the log data sets 106, gathering system statistics, handling startup and shutdown operations, and providing management support. At the center of the DB2 product architecture is the Database Services Processor module 114. The Database Services Processor module contains several submodules, including the Relational Database System (RDS) 116, the Data Manager 118, and the Buffer Manager 120, and other database components 122 including an SQL compiler/interpreter. These submodules support the functions of the SQL language, such as definitions, access control, retrieval, and update of user and system data. The Database Services Processor module 114 preferably comprises one or more processors that execute a series of computer-executable programming instructions. These programming instructions preferably reside in storage locations such as fast-access memory (not shown) of the computer 102. Alternatively, the instructions may be stored on a computer diskette, direct access storage device, magnetic tape, conventional "hard drive", electronic read-only memory, optical storage device, paper punch cards, or another suitable data storage medium (not shown) containing logically segmented storage locations. As noted above, the SQL query processor of the RDBMS will respond to a user query having a full outer join by transforming the full outer join into a left outer join unioned with a right outer join minus the matched tuples. The transformed query is then provided to the SQL optimizer of the RDBMS, for use with known optimization techniques. In a preferred embodiment, such processing can take place in either an interactive operating mode or in a batch processing mode, as described below. Interactive SQL Execution FIG. 2 is a flow diagram that illustrates the operating steps necessary for the interpretation and execution of SQL statements in an interactive network environment. The first flow diagram box of FIG. 2, numbered 202, represents the input of an SQL statement into the computer system from the user. The next flow diagram box 204 of FIG. 2 represents the step of compiling or interpreting the received SQL statement. This step may include a transformation function that transforms the SQL query in a manner described in greater detail below to process full outer joins. The FIG. 2 flow diagram box numbered 206 represents the step of generating a compiled set of runtime structures called an application plan from the compiled SQL statements. Generally, the SQL statements received as input from the user (step 202) specify the data the user wants, but not how to get to it. Generation of the application plans involve consideration of both the available access paths (indexes, sequential reads, etc.) and system held statistics on the data to be accessed (the size of the table, the number of distinct values in a particular column, etc.), to choose what the RDBMS processor considers to be the most efficient access path for the query. The FIG. 2 step represented by the flow diagram box numbered 208 represents the execution of the application plan. The last block 210 in FIG. 2 represents the output of the results of the application plan to the user. Embedded/Batch SQL Execution FIG. 3 is a flow diagram that illustrates the steps necessary for the interpretation and execution of SQL statements embedded in source code for batch operation according to the present invention. The first block 302 represents program source code containing a host language (such as COBOL or C) and embedded in SQL statements that is received by the RDBMS processor for batch processing. The received program source code is next subjected to a pre-compile step 304. There are two types of output code from the pre-compile step 304: a modified SQL source module 306 and a Database Request Module ("DBRM") 308. The modified SQL source module 306 contains host language calls to the DB2 program, which the precompile step 304 inserts into the pre-compile output code in place of the SQL source statements. The other output of the pre-compile step, the DBRM 308, consists of the SQL statements from the program source code 302. After the modified source 306 is produced, a compile and link-edit step 310 uses the modified source output to produce a load module 312, while an optimize and bind step 314 uses the DBRM output 308 to produce a compiled set of runtime structures for the application plan 316. SQL statements from the program source code 302 specify only the data that the user wants, but not how to get to it. Generally, the optimize and bind step 314 optimizes the SQL query. The optimize and bind step then considers both the available access paths (indexes, sequential reads, etc.) and system held statistics on the data to be accessed (the size of the table, the number of distinct values in a particular column, etc.), to choose what it considers to be the most efficient access path for the query. The load module 312 and application plan 316 are then executed together at the last step, represented by the flow diagram box numbered 318. Operation of the Invention A first preferred embodiment of the present invention devises, for the first time, a mechanism which enables a query implementing a full outer join with any join conditions, including but not necessarily limited to the inequality predicate and disjunction. This is enabled by equating, in a novel manner, a full outer join with a left outer join unioned with a right outer join less the matched tuples from the right outer join. Referring to FIG. 4, a flow diagram of this embodiment is shown. At 401, the routine is started, and at 402, the outer join to be performed is identified. At 403, the full outer join is programmatically transformed into a union of a left outer join and a right outer join, minus the matched tuples from the right outer join. The rewritten SQL statement which implements the transformed full outer join is provided to the RDBMS' optimizer (not shown) at 404, and program execution continues at 405. Consider a full outer join with join condition P(T1, T2):
______________________________________
SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM T1 q1 FULL OUTER JOIN T2 q2
ON P(T1,T2)
______________________________________
Having continued reference to FIG. 4, the transformation of this full outer join is programmatically implementable with the following set of SQL statements. These transform the full outer join into a union of a left outer join and a select query with a "not-exists" subquery, as follows:
______________________________________
SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM T1 q1 LEFT OUTER JOIN T2 q2
ON P(T1,T2)
UNION ALL
SELECT null, null, q2.c1, q2.c2
FROM T2 q2
WHERE NOT EXISTS (SELECT 1
FROM T1 q1
WHERE P(T1,T2))
______________________________________
The second operand of the union query returns T2 rows which do not match any rows in T1. It is recognized that transforming a full outer join into a union query with a not-exists subquery may not provide optimally efficient computational performance. This is due to the fact that many subqueries are inherently slow to execute, especially in the MPP shared-nothing environment. Accordingly, the second preferred embodiment of the present invention presents a novel and computationally efficient full outer join with an arbitrary join condition capability. Having reference to FIG. 5, this embodiment of the present invention also transforms the full join query into a union query of left outer join and right outer join, at 501-503. However, in order to ensure that the query returns the correct answer, for the right outer join all the matched rows are filtered out from 504-506. To indicate that the output from the right outer join is a matched row, a non-nullable column of the null-producing operand is added to the output of the right outer join at 504. The non-nullable column includes a primary key (key), and a row identifier (rid) or tuple identifier (tid). The value of these columns is set to null at 505 when there is not a match on the tuple-preserving operand by the definition of the outer join. By applying the "IS NULL" predicate after the right outer join at 506, all matched rows are removed from the answer set. The rewritten SQL statement which implements the transformed full outer join is provided to the RDBMS' optimizer (not shown) at 507, and program execution continues at 508. Consider the following three queries:
______________________________________
I. SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM T1 q1 LEFT OUTER JOIN T2 q2
ON P(T1,T2)
UNION ALL
(SELECT q.c1, q.c2, q.c3, q.c4
FROM TABLE(SELECT q3.c1, q3.c2, q4.c1, q4.c2, q3.tid
FROM T1 q3 RIGHT OUTER JOIN T2 q4
ON P(T1,T2)) as q(c1, c2, c3, c4, tid)
WHERE q.tid IS NULL)
II. SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM T1 q1 LEFT OUTER JOIN T2 q2
ON P(T1,T2)
UNION ALL
(SELECT q.c1, q.c2, q.c3, q.c4
FROM TABLE(SELECT q3.c1, q3.c2, q4.c1, q4.c2, q3.rid
FROM T1 q3 RIGHT OUTER JOIN T2 q4
ON P(T1,T2)) as q(c1, c2, c3, c4, rid)
WHERE q.rid IS NULL)
III. SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM T1 q1 LEFT OUTER JOIN T2 q2
ON P(T1,T2)
UNION ALL
(SELECT q.c1, q.c2, q.c3, q.c4
FROM TABLE(SELECT q3.c1, q3.c2, q4.c1, q4.c2, q3.key
FROM T1 q3 RIGHT OUTER JOIN T2 q4
ON P(T1,T2)) as q(c1, c2, c3, c4, key)
WHERE q.key IS NULL)
______________________________________
It will be appreciated that each of these three queries returns the same answer set. The preceding approach is acceptable given a non-nullable column. FIG. 6 details another preferred embodiment useful where an outer join operand does not have any non-nulable column, for instance when the operand is a derived table, a column of constants could be added to achieve the same effect. For example, suppose both DT1 and DT2 are not base tables: i.e., their row identifiers were not readily avialable, and all columns were nullable. A full outer join for such a condition could then be rewritten as:
______________________________________
SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM DT1 q1 FULL OUTER JOIN DT2 q2
ON P(DT1,DT2)
______________________________________
which in turn can be implemented as:
______________________________________
SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM DT1 q1 LEFT OUTER JOIN DT2 q2
ON P(DT1,DT2)
UNION ALL
(SELECT q.c1, q.c2, q.c3, q.c4
FROM TABLE(SELECT q3.c1, q3.c2, q4.c1, q4.c2, q3.key
FROM TABLE(SELECT DT1.c1, DT1.c2, 1 FROM DT1)as
q3(c1, c2, key)
RIGHT OUTER JOIN DT2 q4 ON P(DT1,DT2)) as q(c1,c2,
c3, c4, key)
WHERE q.key IS NULL)
______________________________________
Having continued reference to FIG. 6, this embodiment of the present invention also transforms the full join query into a union query of left outer join and right join, at 601-603. However, in order to ensure that the query returns the correct answer, for the right outer join all the matched rows are filtered out from 604-606. By adding a column of constant 1 at 604, or any other constant, in the null-producing operand of the right outer join, it is assured that the column value is null for the preserved tuples of DT2 (q4) at 605. By applying the "IS NULL" predicate after the right outer join at 606, all matched rows are removed from the answer set. The rewritten SQL statement which implements the transformed full outer join is provided to the RDBMS' optimizer (not shown) at 607, and program execution continues at 608. Hence, those DT2 rows that do not have any matching rows in DT1 are always selectable. This then discloses the third preferred embodiment of the present invention. A fourth preferred embodiment of the present invention is illustrated at FIG. 7. Given that the present invention implements a full outer join as a union of a left outer join and a right outer join, this embodiment further optimizes the full outer join methodology taught herein by recognizing that all the matched rows in the right outer join are to be filtered out. The right outer join operand of the union query thus becomes a novel operand, the anti-join defined below, which returns all rows in the tuple-preserving table which have no match in the null-producing table. The anti-join is defined as follows: Given tables T1 and T2, and ajoin condition P(T1, T2), the anti-join (T1, T2, P(T1, T2)) returns all T1 tuples that do not match with T2 tuples using join condition P(T1, T2). Having continued reference to FIG. 7, in this embodiment after the full outer join is identified at 702, it is transformed as the union of a left outer join and an anti-join at 703. The rewritten SQL statement which implements the transformed full outer join is provided to the RDBMS' optimizer (not shown) at 704, and program execution continues at 705. It should be noted that the anti-join is a novel operator, and does not form part of the standard SQL syntax, as currently defined. Anti join can be implemented directly using a new run time operator. Alternatively, an anti-join can be implemented using either a right or left outer join coupled with an ISNULL predicate on the non-nullable column of the null-producing operand. However, computing all the matched rows which will be eventually filtered out using prior methodologies requires considerable time and is hence computationally inefficient. Accordingly, the anti-join can be implemented more efficiently by a left or right outer join algorithm with early out capability: i.e., for a given row in a tuple-preserving table, the row will be output as soon as the first match in the null-producing table is found or there is no match in the null-producing table. Note that a left or right outer join algorithm with early out capability CAN NOT be used for implementing a regular left or right outer join because not ALL the matching tuple pairs will be returned in the answer set. Given that the present invention implements a full outer join as a union of a left and a right outer join, the fourth preferred embodiment further optimizes the full outer join methodology taught herein by recognizing that all the matched rows in the right outer join are to be filtered out. In essence, the second operand of the union query is an anti-join, which is implemented as a right outer join coupled with an ISNULL predicate. Computing all matched rows which will be eventually filtered out can be very time consuming. Thus, one can implement the anti-join more efficiently by an outer join algorithm with early out capability. This leads to the following query rewrite transformation. Given a view or derived table of left/right outer join which outputs a column of null-producing operand, and the column is not nullable:
______________________________________
CREATE VIEW DT ( . . . , C1) AS
(SELECT . . . , T2.C1 /* non-nullable C1 */
FROM T1 LEFT OUTER JOIN T2
ON P)
______________________________________
If an ISNULL predicate is found involving the above mentioned column, then the early out property of the left outer join is true. For example,
______________________________________
SELECT *
FROM DT
WHERE C1 IS NULL
______________________________________
Thus, the implementation of the left outer join in DT has an early out property. The result remains the same, but the execution of the left outer join is much faster because as soon as there is a match in T2, the T1 row, which is then filtered out, can be output to the answer set. This avoids computing all matching rows before filtering. To implement a full outer join using anti-join, consider a full outer join discussed above with join condition P(T1, T2):
______________________________________
SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM T1 q1 FULL OUTER JOIN T2 q
ON P(T1,T2)
______________________________________
The former statement can be rewritten as:
______________________________________
SELECT q1.c1, q1.c2, q2.c1, q2.c2
FROM T1 q1 LEFT OUTER JOIN T2 q2
ON P(T1,T2)
UNION ALL
(SELECT q.c1, q.c2, q.c3, q.c4
FROM TABLE(SELECT q3.c1, q3.c2, q4.c1, q4.c2, q3.tid
FROM T1 q3 RIGHT OUTER JOIN T2 q4
ON P(T1,T2)) as q(c1, c2, c3, c4, tid)
WHERE q.tid IS NULL)
______________________________________
It will be appreciated in this fourth preferred embodiment of the present invention that the RIGHT OUTER JOIN is marked with an early-out property and thus the anti-join is implemented. The same principle applies to other types of non-nullable columns, including tid, rid, key or a derived table with an extra column of a constant as previously discussed. It should be emphasized that because the approach taught herein for implementing a full outer join utilizes the properties of the left/right outer join, there is no restriction on the join condition for the full outer join because there is no restriction on the join condition for the left/right outer join which enables it. Moreover, the principles of the present invention specifically contemplate their utilization in interactive, batch, and embedded modes of processing, as well as all other processing methodologies well known to those having ordinary skill in the art. While this invention is primarily discussed as a method, it will be understood by a person of ordinary skill in the art that the apparatus discussed above in connection with FIG. 1 may be programmed or otherwise designed to facilitate the practice of the method of this invention. Further, that an article of manufacture, such as a recordable media for use with a data processing system including but not limited to floppy disks, magnetic tape, optical storage and retrieval media including CD-ROMs and others well known to those having ordinary skill in the art, can be used to implement the invention disclosed herein. It should be well understood that all such apparatus and articles of manufacture also fall within the spirit and scope of this invention. The present invention has been particularly shown and described with respect to certain preferred embodiments of features thereof. Clearly, other embodiments and modifications of this invention may occur readily to those of ordinary skill in the art in view of these teachings. It should therefore be readily apparent to those of ordinary skill in the art that various changes and modifications in form and detail may be made without departing from the spirit and scope of the invention as set forth in the appended claims. Therefore, this invention is limited only by the following claims, which include all such embodiments and modifications when viewed in conjunction with the above specification and accompanying drawing, and the invention disclosed herein may be practiced without any element which is not specifically disclosed herein. In particular, the principles of the present invention specifically contemplate the use of alternative query languages, computer architectures, and database management systems.
|
Same subclass Same class Consider this |
||||||||||