SELECT语句

Select语句由select，from，where，group by，having，order by，union等部分组成，Palo的查询语句基本符合SQL92标准，下面详细介绍支持的select用法。

连接(Join)

连接操作是合并2个或多个表的数据，然后返回其中某些表中的某些列的结果集。目前Palo支持inner join，outer join，semi join，anti join, cross join。在inner join条件里除了支持等值join，还支持不等值join，为了性能考虑，推荐使用等值join。其它join只支持等值join。

语法：

  1. SELECT select_list FROM
  2.  table_or_subquery1 [INNER] JOIN table_or_subquery2 |
  3.  table_or_subquery1 {LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]} JOIN table_or_subquery2 |
  4.  table_or_subquery1 {LEFT | RIGHT} SEMI JOIN table_or_subquery2 |
  5.  table_or_subquery1 {LEFT | RIGHT} ANTI JOIN table_or_subquery2 |
  6.            [ ON col1 = col2 [AND col3 = col4 ...] |
  7.                  USING (col1 [, col2 ...]) ]
  8.  [other_join_clause ...]
  9.  [ WHERE where_clauses ]
  1. SELECT select_list FROM
  2.  table_or_subquery1, table_or_subquery2 [, table_or_subquery3 ...]
  3.  [other_join_clause ...]
  4. WHERE
  5.        col1 = col2 [AND col3 = col4 ...]
  1. SELECT select_list FROM
  2.  table_or_subquery1 CROSS JOIN table_or_subquery2
  3.  [other_join_clause ...]
  4. [ WHERE where_clauses ]

Self-Join

Palo支持self-joins，即自己和自己join。例如同一张表的不同列进行join。实际上没有特殊的语法标识self-join。self-join中join两边的条件都来自同一张表，我们需要给他们分配不同的别名。

举例：

  1. SELECT lhs.id, rhs.parent, lhs.c1, rhs.c2 FROM tree_data lhs, tree_data rhs WHERE lhs.id = rhs.parent;

笛卡尔积(Cross Join)

Cross join会产生大量的结果，须慎用cross join，即使需要使用cross join时也需要使用过滤条件并且确保返回结果数较少。

举例：

  1. SELECT * FROM t1, t2;
  2. SELECT * FROM t1 CROSS JOIN t2;

Inner join

inner join 是大家最熟知，最常用的join。返回的结果来自相近的2张表所请求的列，join 的条件为两个表的列包含有相同的值。如果两个表的某个列名相同，我们需要使用全名（table_name.column_name形式）或者给列名起别名。

举例：

  1. -- The following 3 forms are all equivalent.
  2. SELECT t1.id, c1, c2 FROM t1, t2 WHERE t1.id = t2.id;
  3. SELECT t1.id, c1, c2 FROM t1 JOIN t2 ON t1.id = t2.id;
  4. SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id = t2.id;

Outer join

outer join返回左表或者右表或者两者所有的行。如果在另一张表中没有匹配的数据，则将其设置为NULL。

举例：

  1. SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id;
  2. SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id;
  3. SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;

等值和不等值join

通常情况下，用户使用等值join居多，等值join要求join条件的操作符是等号。不等值join 在join条件上可以使用!=，<, >等符号。不等值join会产生大量的结果，在计算过程中可能超过内存限额，因此需要谨慎使用。不等值join只支持inner join。

举例：

  1. SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id != t2.id;
  2. SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id > t2.id;

Semi join

left semi join只返回左表中能匹配右表数据的行，不管能匹配右表多少行数据，左表的该行最多只返回一次。right semi join原理相似，只是返回的数据是右表的。

举例：

  1. SELECT t1.c1, t1.c2, t1.c2 FROM t1 LEFT SEMI JOIN t2 ON t1.id = t2.id;

Anti join

left anti join只返回左表中不能匹配右表的行。right anti join反转了这个比较，只返回右表中不能匹配左表的行。

举例：

  1. SELECT t1.c1, t1.c2, t1.c2 FROM t1 LEFT ANTI JOIN t2 ON t1.id = t2.id;

Order by

order by通过比较1列或者多列的大小来对结果集进行排序。order by是比较耗时耗资源的操作，因为所有数据都需要发送到1个节点后才能排序，排序操作相比不排序操作需要更多的内存。如果需要返回前N个排序结果，需要使用LIMIT从句；为了限制内存的使用，如果用户没有指定LIMIT从句，则默认返回前65535个排序结果。

语法：

  1. ORDER BY col [ASC | DESC]

默认的排序是ASC

举例：

  1. mysql> select * from big_table order by tiny_column, short_column desc;

Group by

group by从句通常和聚合函数（例如COUNT(), SUM(), AVG(), MIN()和MAX()）一起使用。group by指定的列不会参加聚合操作。group by从句可以加入having从句来过滤聚合函数产出的结果。

举例：

  1. mysql> select tiny_column, sum(short_column) from small_table group by tiny_column;
  2. +-------------+---------------------+
  3. | tiny_column | sum(`short_column`) |
  4. +-------------+---------------------+
  5. |           1 |                   2 |
  6. |           2 |                   1 |
  7. +-------------+---------------------+
  8. 2 rows in set (0.07 sec)

Having

having从句不是过滤表中的行数据，而是过滤聚合函数产出的结果。通常来说having要和聚合函数（例如COUNT(), SUM(), AVG(), MIN(), MAX()）以及group by从句一起使用。

举例：

  1. mysql> select tiny_column, sum(short_column) from small_table group by tiny_column having sum(short_column) = 1;
  2. +-------------+---------------------+
  3. | tiny_column | sum(`short_column`) |
  4. +-------------+---------------------+
  5. |           2 |                   1 |
  6. +-------------+---------------------+
  7. 1 row in set (0.07 sec)
  1. mysql> select tiny_column, sum(short_column) from small_table group by tiny_column having tiny_column > 1;
  2. +-------------+---------------------+
  3. | tiny_column | sum(`short_column`) |
  4. +-------------+---------------------+
  5. |           2 |                   1 |
  6. +-------------+---------------------+
  7. 1 row in set (0.07 sec)

Limit

Limit从句用于限制返回结果的最大行数。设置返回结果的最大行数可以帮助Palo优化内存的使用。该从句主要应用如下场景：

  • 返回top-N的查询结果。

  • 想简单看下表中包含的内容。

  • 如果表中数据足够大，或者where从句没有过滤太多的数据，需要使用

使用说明：

limit从句的值必须是数字型字面常量。

举例：

  1. mysql> select tiny_column from small_table limit 1;
  2. +-------------+
  3. | tiny_column |
  4. +-------------+
  5. |           1 |
  6. +-------------+
  7. 1 row in set (0.02 sec)
  1. mysql> select tiny_column from small_table limit 10000;
  2. +-------------+
  3. | tiny_column |
  4. +-------------+
  5. |           1 |
  6. |           2 |
  7. +-------------+
  8. 2 rows in set (0.01 sec)

Offset

offset从句使得结果集跳过前若干行结果后直接返回后续的结果。结果集默认起始行为第0行，因此offset 0和不带offset返回相同的结果。通常来说，offset从句需要与order by从句和limit从句一起使用才有效。

举例：

  1. mysql> select varchar_column from big_table order by varchar_column limit 3;
  2. +----------------+
  3. | varchar_column |
  4. +----------------+
  5. | beijing        |
  6. | chongqing      |
  7. | tianjin        |
  8. +----------------+
  9. 3 rows in set (0.02 sec)
  1. mysql> select varchar_column from big_table order by varchar_column limit 1 offset 0;
  2. +----------------+
  3. | varchar_column |
  4. +----------------+
  5. | beijing        |
  6. +----------------+
  7. 1 row in set (0.01 sec)
  1. mysql> select varchar_column from big_table order by varchar_column limit 1 offset 1;
  2. +----------------+
  3. | varchar_column |
  4. +----------------+
  5. | chongqing      |
  6. +----------------+
  7. 1 row in set (0.01 sec)
  1. mysql> select varchar_column from big_table order by varchar_column limit 1 offset 2;
  2. +----------------+
  3. | varchar_column |
  4. +----------------+
  5. | tianjin        |
  6. +----------------+
  7. 1 row in set (0.02 sec)

注意：

在没有order by的情况下使用offset语法是允许的，但是此时offset无意义，这种情况只取limit的值，忽略掉offset的值。因此在没有order by的情况下，offset超过结果集的最大行数依然是有结果的。建议用户使用offset时一定要带上order by。

Union

Union从句用于合并多个查询的结果集。

语法：

  1. query_1 UNION [DISTINCT | ALL] query_2

使用说明：

只使用union关键词和使用union disitnct的效果是相同的。由于去重工作是比较耗费内存的，因此使用union all操作查询速度会快些，耗费内存会少些。如果用户想对返回结果集进行order by和limit操作，需要将union操作放在子查询中，然后select from subquery，最后把subgquery和order by放在子查询外面。

举例：

  1. mysql> (select tiny_column from small_table) union all (select tiny_column from small_table);
  2. +-------------+
  3. | tiny_column |
  4. +-------------+
  5. |           1 |
  6. |           2 |
  7. |           1 |
  8. |           2 |
  9. +-------------+
  10. 4 rows in set (0.10 sec)
  1. mysql> (select tiny_column from small_table) union (select tiny_column from small_table);
  2. +-------------+
  3. | tiny_column |
  4. +-------------+
  5. |           2 |
  6. |           1 |
  7. +-------------+
  8. 2 rows in set (0.11 sec)
  1. mysql> select * from (select tiny_column from small_table union all\
  2.        -> select tiny_column from small_table) as t1 \
  3.        -> order by tiny_column limit 4;
  4. +-------------+
  5. | tiny_column |
  6. +-------------+
  7. |           1 |
  8. |           1 |
  9. |           2 |
  10. |           2 |
  11. +-------------+
  12. 4 rows in set (0.11 sec)

Distinct

Distinct操作符对结果集进行去重。

举例：

  1. mysql> -- Returns the unique values from one column.
  2. mysql> select distinct tiny_column from big_table limit 2;
  3. mysql> -- Returns the unique combinations of values from multiple columns.
  4. mysql> select distinct tiny_column, int_column from big_table limit 2;

distinct可以和聚合函数(通常是count函数)一同使用，count(disitnct)用于计算出一个列或多个列上包含多少不同的组合。

  1. mysql> -- Counts the unique values from one column.
  2. mysql> select count(distinct tiny_column) from small_table;
  3. +-------------------------------+
  4. | count(DISTINCT `tiny_column`) |
  5. +-------------------------------+
  6. |                             2 |
  7. +-------------------------------+
  8. 1 row in set (0.06 sec)
  9. mysql> -- Counts the unique combinations of values from multiple columns.
  10. mysql> select count(distinct tiny_column, int_column) from big_table limit 2;

Palo支持多个聚合函数同时使用distinct

  1. mysql> -- Count the unique value from multiple aggregation function separately.
  2. mysql> select count(distinct tiny_column, int_column), count(distinct varchar_column) from big_table;

子查询

子查询按相关性分为不相关子查询和相关子查询。

不相关子查询

不相关子查询支持[NOT] IN和EXISTS。

举例：

  1. SELECT x FROM t1 WHERE x [NOT] IN (SELECT y FROM t2);
  2. SELECT x FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE y = 1);

相关子查询

相关子查询支持[NOT] IN和[NOT] EXISTS。

举例：

  1. SELECT * FROM t1 WHERE x [NOT] IN (SELECT a FROM t2 WHERE t1.y = t2.b);
  2. SELECT * FROM t1 WHERE [NOT] EXISTS (SELECT a FROM t2 WHERE t1.y = t2.b);

子查询还支持标量子查询。分为不相关标量子查询、相关标量子查询和标量子查询作为普通函数的参数。

举例：

  1. 1、不相关标量子查询，谓词为=号。例如输出最大工资的人的信息
  2. SELECT name FROM table WHERE salary = (SELECT MAX(salary) FROM table);
  3. 2、不相关标量子查询，谓词为>,<等。例如输出比平均工资高的人的信息
  4. SELECT name FROM table WHERE salary > (SELECT AVG(salary) FROM table);
  5. 3、相关标量子查询。例如输出各个部门工资最高的信息
  6. SELECT name FROM table a WHERE salary = （SELECT MAX(salary) FROM table b WHERE b.部门= a.部门）;
  7. 4、标量子查询作为普通函数的参数
  8. SELECT name FROM table WHERE salary = abs((SELECT MAX(salary) FROM table));

with子句

可以在SELECT语句之前添加的子句，用于定义在SELECT内部多次引用的复杂表达式的别名。与CREATE VIEW类似，除了在子句中定义的表和列名在查询结束后不会持久以及不会与实际表或VIEW中的名称冲突。

用WITH子句的好处有：

1.方便和易于维护，减少查询内部的重复。

2.通过将查询中最复杂的部分抽象成单独的块，更易于阅读和理解SQL代码。

举例：

  1. -- Define one subquery at the outer level, and another at the inner level as part of the
  2. -- initial stage of the UNION ALL query.
  3. with t1 as (select 1) (with t2 as (select 2) select * from t2) union all select * from t1;