SELECT

Function

SELECT retrieves data from a table or view.

Serving as an overlaid filter for a database table, SELECT filters required data from the table using SQL keywords.

Precautions

Compared with the MogDB SELECT syntax, the SOUNDS LIKE syntax under the WHERE clause is added.
The new JOIN does not contain ON/USING. The effect is the same as that of CROSS JOIN.

Syntax

Querying data

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
{ * | {expression [ [ AS ] output_name ]} [, ...] }
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ [ START WITH condition ] CONNECT BY [NOCYCLE] condition [ ORDER SIBLINGS BY expression ] ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW {window_name AS ( window_definition )} [, ...] ]
[ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
[ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
[ LIMIT { [offset,] count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ {FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT ]} [...] ];

The group clause is as follows:

( )
| expression
| ( expression [, ...] )
| rollup_clause
| CUBE ( { expression | ( expression [, ...] ) } [, ...] )
| GROUPING SETS ( grouping_element [, ...] )

The rollup_clause clause is as follows:

ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
| { expression | ( expression [, ...] ) } WITH ROLLUP

JOIN syntax

[JOIN | INNER JOIN] {ON join_condition | USING ( join_column [, ...] ) }

Parameter Description

WHERE clause
1. SOUNDS LIKE is a syntax of condition. For example, column_name sounds like 'character'; is equivalent to the comparison result of soundex(column_name) = soundex('character'). It is a Boolean value. It is used to query the data that meets the conditions through soundex processing.
NOTE: For details about other parameters, see SELECT.

Examples

–Example of the SOUNDS LIKE: homophone column query

MogDB=# CREATE TABLE TEST(id int, name varchar);
MogDB=# INSERT INTO TEST VALUES(1, 'too');
MogDB=# SELECT * FROM TEST WHERE name SOUNDS LIKE 'two';
 id | name
----+------
  1 | too
(1 row)
--Use ROLLUP in the SELECT GROUP BY clause.
MogDB=# CREATE TABLESPACE t_tbspace ADD DATAFILE 'my_tablespace' ENGINE = test_engine;
CREATE TABLESPACE
MogDB=# CREATE TABLE t_with_rollup(id int, name varchar(20), area varchar(50), count int);
CREATE TABLE
MogDB=# INSERT INTO t_with_rollup values(1, 'a', 'A', 10);
INSERT 0 1
MogDB=# INSERT INTO t_with_rollup values(2, 'b', 'B', 15);
INSERT 0 1
MogDB=# INSERT INTO t_with_rollup values(2, 'b', 'B', 20);
INSERT 0 1
MogDB=# INSERT INTO t_with_rollup values(3, 'c', 'C', 50);
INSERT 0 1
MogDB=# INSERT INTO t_with_rollup values(3, 'c', 'C', 15);
INSERT 0 1
MogDB=# SELECT name, sum(count) FROM t_with_rollup GROUP BY ROLLUP(name);
 name | sum
------+-----
 a    |  10
 b    |  35
 c    |  65
      | 110
(4 rows)

MogDB=# SELECT name, sum(count) FROM t_with_rollup GROUP BY (name) WITH ROLLUP;
 name | sum
------+-----
 a    |  10
 b    |  35
 c    |  65
      | 110
(4 rows)

Helpful Links

SELECT