Method and system for outer join of database tables

1. A computer-implemented method comprising:

broadcasting a first data set to a plurality of database nodes storing a second data set, wherein each of the plurality of database nodes stores a portion of the second data set;

dividing the plurality of database nodes storing the second data set into a first subset and a second subset, wherein the first subset comprises at least one but less than all of the plurality of database nodes, the at least one database node storing a least amount of data of the second data set;

obtaining an OUTER JOIN result by executing an OUTER JOIN operation based on the first data set broadcasted to each database node of the first subset and the portion of the second data set stored in the each database node of the first subset;

obtaining an INNER JOIN result by executing an INNER JOIN operation based on the first data set broadcasted to each database node of the second subset and the portion of the second data set stored in the each database node of the second subset; and

returning a result to an OUTER JOIN command by aggregating the OUTER JOIN result from each database node of the first subset and the INTER JOIN result from each database node of the second subset.

2. The method of claim 1 , further comprising:

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.

3. The method of claim 2 , wherein the identifying and removing a first row with a NULL value comprises:

performing a GROUP BY operation to the OUTER JOIN result and the INNER JOIN result based on one or more join keys of the OUTER JOIN command.

4. The method of claim 1 , wherein the first data set is a distributed database data set stored in a plurality of second database nodes, and the broadcasting the first data set to a plurality of database nodes storing the second data set comprises:

broadcasting data from each database node of the plurality of second database nodes to each database node of the plurality of database nodes.

5. The method of claim 1 , wherein the executing the OUTER JOIN operation based on the first data set broadcasted to the each database node of the first subset and the portion of the second data set stored in the each database node of the first subset comprises:

performing the OUTER JOIN operation by using the first data set broadcasted to the each database node of the first subset as an outer data set and the portion of the second data set stored on the each database node of the first subset as an inner data set.

6. The method of claim 1 , wherein the OUTER JOIN operation executed by each of database nodes of the first subset is based on one or more join keys of the OUTER JOIN command; and

the INNER JOIN operation executed by each of database nodes of the second subset is based on the one or more join keys of the OUTER JOIN command.

7. The method of claim 1 , wherein each of the first data set and the second data set comprises at least one of the following: a database table, and an intermediate result of a database operation.

8. A system 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 a first data set to a plurality of database nodes storing a second data set, wherein each of the plurality of database nodes stores a portion of the second data set;

dividing the plurality of database nodes storing the second data set into a first subset and a second subset, wherein the first subset comprises at least one but less than all of the plurality of database nodes, the at least one database node storing a least amount of data of the second data set;

obtaining an OUTER JOIN result by executing an OUTER JOIN operation based on the first data set broadcasted to each database node of the first subset and the portion of the second data set stored in the each database node of the first subset;

obtaining an INNER JOIN result by executing an INNER JOIN operation based on the first data set broadcasted to each database node of the second subset and the portion of the second data set stored in the each database node of the second subset; and

returning a result to an 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.

9. The system of claim 8 , wherein the operations further comprise:

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.

10. The system of claim 9 , wherein the identifying and removing a first row with a NULL value comprises:

performing a GROUP BY operation to the OUTER JOIN result and the INNER JOIN result based on one or more join keys of the OUTER JOIN command.

11. The system of claim 8 wherein the identifying and removing a first row with a NULL value comprises:

performing a GROUP BY operation to the OUTER JOIN result and the INNER JOIN result based on one or more join keys of the OUTER JOIN command.

12. The system of claim 8 , wherein the first data set is a distributed database data set stored in a plurality of second database nodes, and the broadcasting the first data set to a plurality of database nodes storing the second data set comprises:

broadcasting data from each database node of the plurality of second database nodes to each database node of the plurality of database nodes.

13. The system of claim 8 , wherein the executing the OUTER JOIN operation based on the first data set broadcasted to the each database node of the first subset and the portion of the second data set stored in the each database node of the first subset comprises:

performing the OUTER JOIN operation by using the first data set broadcasted to the each database node of the first subset as an outer data set and the portion of the second data set stored on the each database node of the first subset as an inner data set.

14. The system of claim 8 , wherein the OUTER JOIN operation executed by each of database nodes of the first subset is based on one or more join keys of the OUTER JOIN command; and

the INNER JOIN operation executed by each of database nodes of the second subset is based on the one or more join keys of the OUTER JOIN command.

15. A non-transitory computer-readable storage medium, configured with instructions executable by one or more processors to cause the one or more processors to perform operations comprising:

broadcasting a first data set to a plurality of database nodes storing a second data set, wherein each of the plurality of database nodes stores a portion of the second data set;

dividing the plurality of database nodes storing the second data set into a first subset and a second subset, wherein the first subset comprises at least one but less than all of the plurality of database nodes, the at least one database node storing a least amount of data of the second data set;

obtaining an OUTER JOIN result by executing an OUTER JOIN operation based on the first data set broadcasted to each database node of the first subset and the portion of the second data set stored in the each database node of the first subset;

obtaining an INNER JOIN result by executing an INNER JOIN operation based on the first data set broadcasted to each database node of the second subset and the portion of the second data set stored in the each database node of the second subset; and

returning a result to an 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.

16. The non-transitory computer-readable storage medium of claim 15 , wherein the operations further comprise:

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.

17. The non-transitory computer-readable storage medium of claim 16 , wherein the identifying and removing a first row with a NULL value comprises:

performing a GROUP BY operation to the OUTER JOIN result and the INNER JOIN result based on one or more join keys of the OUTER JOIN command.

18. The non-transitory computer-readable storage medium of claim 15 , wherein the identifying and removing a first row with a NULL value comprises:

performing a GROUP BY operation to the OUTER JOIN result and the INNER JOIN result based on one or more join keys of the OUTER JOIN command.

19. The non-transitory computer-readable storage medium of claim 15 , wherein the first data set is a distributed database data set stored in a plurality of second database nodes, and the broadcasting the first data set to a plurality of database nodes storing the second data set comprises:

broadcasting data from each database node of the plurality of second database nodes to each database node of the plurality of database nodes.

20. The non-transitory computer-readable storage medium of claim 15 , wherein the executing the OUTER JOIN operation based on the first data set broadcasted to the each database node of the first subset and the portion of the second data set stored in the each database node of the first subset comprises:

performing the OUTER JOIN operation by using the first data set broadcasted to the each database node of the first subset as an outer data set and the portion of the second data set stored on the each database node of the first subset as an inner data set.