OceanBase Join操作

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输出结果时候的欣喜，到多层嵌套查询轻松搞定时的淡定，真是体会了团队作战的威力！