Optimization of correlated SQL queries in a relational database management system

A method of processing a query in a relational database management system that optimizes the query to retrieve data from computer storage, the method comprising the steps of:

• receiving a correlated subquery;
• performing a decorrelation operation on the correlated subquery;
• determining if a current subquery operation being decorrelated is a pass-through SELECT operation;
• and
• executing a query pass-through optimization process to eliminate a join operation for a floating SELECT operation that does not reference any base table.

A relational database management system comprising:

• a central processing unit at a first node of a computer network;
• and
• a query processor that receives relational queries at the first node and optimizes them to retrieve data from the data storage devices, wherein the query processor evaluates queries by performing the steps of:
• receiving a correlated subquery;
• performing a decorrelation operation on the correlated subquery;
• determining if outer join operation was created by the decorrelation operation;
• and
• responding to a created outer join operation by converting the created outer join operation of the decorrelated subquery into a correlated scalar derived table using a COALESCE function, wherein the input to the function is the result from the decorrelated subquery, and correlation bindings of the scalar derived table are obtained from the correlation source directly and not from a magic operation.

A relational database management system comprising:

• a central processing unit at a first node of a computer network;
• and
• a query processor that receives relational queries at the first node and optimizes them to retrieve data from the data storage devices, wherein the query processor optimizes by performing the steps of:
• receiving a correlated subquery;
• performing a decorrelation operation on the correlated subquery;
• and
• determining if a current subquery operation being decorrelated is a pass-through SELECT operation;
• and
• executing a query pass-through optimization process to eliminate a join operation for a floating SELECT operation that does not reference any base table.

A program product data storage device, tangibly embodying a program of machine-readable instructions executable by a computer system connected to a computer network, the program product comprising:

• a recordable media;
• a relational data base management system (RDBMS) recorded on the recordable media, the RDBMS having a query processor that evaluates queries by performing the steps of
• receiving a correlated subquery;
• performing a decorrelation operation on the correlated subquery;
• determining if an outer join operation was created by the decorrelation operation;
• and
• responding to a created outer join operation by converting the created outer join operation of the decorrelated subquery into a correlated scalar derived table using a COALESCE function, wherein the input to the function is the result from the decorrelated subquery, and correlation bindings of the scalar derived table are obtained from the correlation source directly and not from a magic operation.

A program product data storage device, tangibly embodying a program of machine-readable instructions executable by a computer system connected to a computer network, the program product comprising:

• a recordable media;
• a relational data base management system (RDBMS) recorded on the recordable media, the RDBMS having a query processor that processes the query by performing the steps of
• receiving a correlated subquery;
• performing a decorrelation operation on the correlated subquery;
• and
• determining if a current subquery operation being decorrelated is a pass-through SELECT operation;
• and
• executing a query pass-through optimization process to eliminate a join operation of a floating SELECT operation that does not reference any base table.