OceanBase Join操作

最新推荐文章于 2025-10-31 20:17:21 发布

原创最新推荐文章于 2025-10-31 20:17:21 发布 · 3.4k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#join #null #mysql #table #sql

分布式系统专栏收录该内容

70 篇文章

订阅专栏

本文详细介绍了 OceanBase 0.4 版本中全面支持的 SQL 功能，特别关注了 Join 操作的实现，包括 fullJoin 的支持。通过具体示例展示了如何使用 MySQL 客户端连接 OceanBase 并执行 Join 查询，如左连接、右连接和全连接，并提供了与 OceanBase 相关的其他 SQL 用法。

OceanBase 0.4版本中将全面支持SQL，下面给出OceanBase对Join的支持的例子。其中最值得关注的是，OceanBase支持full Join哦：）

从下面的截图还可以看出来，用的是mysql客户端连接OceanBase。OceanBase完全兼容mysql通信协议。

mysql> select * from j1;
+---------+------+
| k1      | v1   |
+---------+------+
| jasimin | NULL |
| raywill |  101 |
+---------+------+
2 rows in set (0.01 sec)

mysql> select * from j2;
+---------+------+
| k2      | v1   |
+---------+------+
|     eng |  101 |
| officer |  102 |
+---------+------+
2 rows in set (0.01 sec)


mysql> select * from j1 join j2 on j1.v1 = j2.v1;
+---------+------+------+------+
| k1      | v1   | k2   | v1   |
+---------+------+------+------+
| raywill |  101 |  eng |  101 |
+---------+------+------+------+
1 row in set (0.08 sec)

mysql> select * from j1 left join j2 on j1.v1 = j2.v1;
+---------+------+------+------+
| k1      | v1   | k2   | v1   |
+---------+------+------+------+
| jasimin | NULL | NULL | NULL |
| raywill |  101 |  eng |  101 |
+---------+------+------+------+
2 rows in set (0.01 sec)

mysql> select * from j1 right join j2 on j1.v1 = j2.v1;    
+---------+------+---------+------+
| k1      | v1   | k2      | v1   |
+---------+------+---------+------+
| raywill |  101 |     eng |  101 |
|    NULL | NULL | officer |  102 |
+---------+------+---------+------+
2 rows in set (0.01 sec)

mysql> select * from j1 full join j2 on j1.v1 = j2.v1;      
+---------+------+---------+------+
| k1      | v1   | k2      | v1   |
+---------+------+---------+------+
| jasimin | NULL |    NULL | NULL |
| raywill |  101 |     eng |  101 |
|    NULL | NULL | officer |  102 |
+---------+------+---------+------+
3 rows in set (0.02 sec)

再举一个例子：

mysql> select * from person;
+------+---------+
| id   | name    |
+------+---------+
|    1 | raywill |
|    2 | kaka    |
|    3 | NULL    |
+------+---------+
3 rows in set (0.00 sec)

mysql> select * from order_table;
+------+------+---------------+
| oid  | id   | order_item_id |
+------+------+---------------+
|    1 |    1 |             1 |
|    2 |    1 |             2 |
|    3 |    2 |             1 |
|    4 | NULL |             1 |
+------+------+---------------+
4 rows in set (0.00 sec)

mysql> select person.name, order_table.order_item_id from person LEFT JOIN order_table ON person.id = order_table.id;
+---------+---------------+
| name    | order_item_id |
+---------+---------------+
| raywill |             1 |
| raywill |             2 |
| kaka    |             1 |
| NULL    |          NULL |
+---------+---------------+
4 rows in set (0.01 sec)

mysql> select person.name, order_table.order_item_id from person RIGHT JOIN order_table ON person.id = order_table.id;
+---------+---------------+
| name    | order_item_id |
+---------+---------------+
| NULL    |             1 |
| raywill |             1 |
| raywill |             2 |
| kaka    |             1 |
+---------+---------------+
4 rows in set (0.01 sec)

mysql> select person.name, order_table.order_item_id from person INNER JOIN order_table ON person.id = order_table.id;
mysql> select person.name, order_table.order_item_id from person JOIN order_table ON person.id = order_table.id;  // 等价inner join
+---------+---------------+
| name    | order_item_id |
+---------+---------------+
| raywill |             1 |
| raywill |             2 |
| kaka    |             1 |
+---------+---------------+
3 rows in set (0.00 sec)



mysql> select person.name, order_table.order_item_id from person FULL JOIN order_table ON person.id = order_table.id;       
+---------+---------------+
| name    | order_item_id |
+---------+---------------+
| NULL    |             1 |
| raywill |             1 |
| raywill |             2 |
| kaka    |             1 |
| NULL    |          NULL |
+---------+---------------+
5 rows in set (0.00 sec)

经过为期半年的努力，从看到select * from simple_table输出结果时候的欣喜，到多层嵌套查询轻松搞定时的淡定，真是体会了团队作战的威力！

期待开源0.4 ；）更多OceanBase信息请关注我们的官网

我的微博：研究员Raywill