Left-right join #

Principle #

DBPlusEngine currently supports left-right join queries for bound tables, broadcast tables and single tables. When performing left-right join queries for bound tables, you need to specify the slice key in the join condition. Slice tables, broadcast tables, and the join query for slice tables and single tables are not supported at the moment and need to rely on the SQL Federation engine to provide query capability.

The relationship table is shown as follows.

Combination	Associated Conditions	Whether to support	Processing Engine
Sharding Table + Sharding Table	Sharding Key + Binding	Yes	Kernel
	Sharding Key + Unbound	Yes	Experimental SQL Federation engine, need to open manually
	Non-Sharding Keys	Yes	Experimental SQL Federation engine, need to open manually
Sharding Table + Single Table	Sharding Key	Yes	Experimental SQL Federation engine, need to open manually
Sharding Table + Single Table	Non-Sliced Keys	Yes	Experimental SQL Federation engine, need to open manually
Slice + Broadcast Table	Slice Key	No	Kernel (bugs, duplicate left and right connection data, inner connection support)
Slice + Broadcast Table	Non-Sliced Keys	No	Kernel (bugs, duplicate left and right connection data, inner connection support)
Single Table + Broadcast Table	Unrestricted	Yes	Kernel
Single Table + Single Table	Unrestricted	Yes	Kernel (same database scenario)
Single Table + Single Table	Unrestricted	Yes	Experimental SQL Federation engine, need to open manually (cross-library scenario)

When DBPlusEngine executes the join query of bound tables, it selects the table with the specified slice condition as the master table for routing rewriting, and the other bound tables are rewritten according to the binding relationship.

Then the rewritten real SQL is pushed down to the database for execution, since the bound tables have the same data distribution and the slice key is specified as the association condition in the query. The query result of native database association query result can be used as the query result of logical SQL, and finally the results of multiple slices are merged by the merge engine.

When executing the join query of the broadcast table and single table, DBPlusEngine will route the single table as the primary table and then push the SQL down to the database for execution to get the result of the association query.

Suggestion #

Configure multiple tables with the same slicing rules as bound tables to ensure that correlation queries can be pushed down to the database for execution.

Example #

The following example shows the associative queries supported by DBPlusEngine. t_order and t_order_item are bound tables. t_config is a broadcast table. And t_single is a single table.

-- Query the data of each table
mysql> select * from t_order;
+----------+---------+---------+
| order_id | user_id | content |
+----------+---------+---------+
| 2 | 2 | TEST2 |
| 4 | 1 | test11 |
| 1 | 1 | %TEST1 |
| 3 | 3 | test3 |
| 5 | 1 | test11 |
+----------+---------+---------+
5 rows in set (0.01 sec)

mysql> select * from t_order_item;
+---------+----------+---------+---------+
| item_id | order_id | content | user_id |
+---------+----------+---------+---------+
| 2 | 2 | TEST2 | 2 |
| 1 | 1 | TEST1 | 1 |
+---------+----------+---------+---------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM t_config;
+-----------+---------+
| config_id | content |
+-----------+---------+
|         1 | TEST1   |
|         2 | TEST2   |
|         3 | TEST3   |
|         4 | TEST4   |
+-----------+---------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM t_single;
+-----------+---------+
| single_id | content |
+-----------+---------+
|         1 | TEST1   |
|         2 | TEST2   |
+-----------+---------+
2 rows in set (0.01 sec)

-- Binding table left join
mysql> select * from t_order o left join t_order_item i on o.order_id = i.order_id and o.user_id = i.user_id;
+----------+---------+---------+---------+----------+---------+---------+
| order_id | user_id | content | item_id | order_id | content | user_id |
+----------+---------+---------+---------+----------+---------+---------+
| 2 | 2 | TEST2 | 2 | 2 | TEST2 | 2 |
| 4 | 1 | test11 | NULL | NULL | NULL | NULL |
| 1 | 1 | %TEST1 | 1 | 1 | TEST1 | 1 |
| 3 | 3 | test3 | NULL | NULL | NULL | NULL |
| 5 | 1 | test11 | NULL | NULL | NULL | NULL |
+----------+---------+---------+---------+----------+---------+---------+
5 rows in set (0.01 sec)

-- Binding table right join
mysql> select * from t_order o right join t_order_item i on o.order_id = i.order_id and o.user_id = i.user_id;
+----------+---------+---------+---------+----------+---------+---------+
| order_id | user_id | content | item_id | order_id | content | user_id |
+----------+---------+---------+---------+----------+---------+---------+
|        2 |       2 | TEST2   |       2 |        2 | TEST2   |       2 |
|        1 |       1 | %TEST1  |       1 |        1 | TEST1   |       1 |
+----------+---------+---------+---------+----------+---------+---------+
2 rows in set (0.01 sec)

-- Broadcast table, single table left join
mysql> SELECT * FROM t_config o LEFT JOIN t_single s ON o.config_id = s.single_id;
+-----------+---------+-----------+---------+
| config_id | content | single_id | content |
+-----------+---------+-----------+---------+
|         1 | TEST1   |         1 | TEST1   |
|         2 | TEST2   |         2 | TEST2   |
|         3 | TEST3   |      NULL | NULL    |
|         4 | TEST4   |      NULL | NULL    |
+-----------+---------+-----------+---------+
4 rows in set (0.00 sec)

-- Broadcast table, single table right join
mysql> SELECT * FROM t_config o RIGHT JOIN t_single s ON o.config_id = s.single_id;
+-----------+---------+-----------+---------+
| config_id | content | single_id | content |
+-----------+---------+-----------+---------+
|         1 | TEST1   |         1 | TEST1   |
|         2 | TEST2   |         2 | TEST2   |
+-----------+---------+-----------+---------+
2 rows in set (0.00 sec)