Method and system for outer join of database tables

A system for executing an outer join command on a first table and a second table in a distributed database, comprising a processor and a non-transitory computer-readable storage medium storing instructions executable by the processor to cause the system to perform operations comprising:

• broadcasting the first table to a plurality of database nodes of a distributed database storing the second table, wherein the second table is a distributed database table, and each of the plurality of database nodes stores a portion of the second table;
• dividing the plurality of database nodes storing the second table into a first subset and a second subset, wherein the first subset and the second subset each comprises less than all of the plurality of database nodes;
• obtaining, by each database node of the first subset, an OUTER JOIN result by executing an OUTER JOIN operation based on the first table broadcasted to the each database node of the first subset and the portion of the second table stored in the each database node of the first subset;
• obtaining, by each database node of the second subset, an INNER JOIN result by executing an INNER JOIN operation based on the first table broadcasted to the each database node of the second subset and the portion of the second table stored in the each database node of the second subset;
• identifying and removing a first row with a NULL value from the OUTER JOIN result when the INNER JOIN result comprises a second row sharing a same join key with the first row;
• and
• returning a result to the OUTER JOIN command by aggregating the OUTER JOIN result from each database node of the first subset and the INNER JOIN result from each database node of the second subset.

A non-transitory computer-readable storage medium for executing an outer join command on a first table and a second table in a distributed database, configured with instructions executable by one or more processors to cause the one or more processors to perform operations comprising:

• broadcasting the first table to a plurality of database nodes of a distributed database storing the second table, wherein the second table is a distributed database table, and each of the plurality of database nodes stores a portion of the second table;
• dividing the plurality of database nodes storing the second table into a first subset and a second subset, wherein the first subset and the second subset each comprises, less than all of the plurality of database nodes;
• obtaining, by each database node of the first subset, an OUTER JOIN result by executing an OUTER JOIN operation based on the first table broadcasted to the each database node of the first subset and the portion of the second table stored in the each database node of the first subset;
• obtaining, by each database node of the second subset, an INNER JOIN result by executing an INNER JOIN operation based on the first table broadcasted to the each database node of the second subset and the portion of the second table stored in the each database node of the second subset;
• identifying and removing a first row with a NULL value from the OUTER JOIN result when the INNER JOIN result comprises a second row sharing a same join key with the first row;
• and returning a result to the OUTER JOIN command by aggregating the OUTER JOIN result from each database node of the first subset and the INNER JOIN result from each database node of the second subset.