SIMPLE SELECT

The syntax of SELECT is relatively complex. This section describes the syntax of SIMPLE SELECT.

Purpose

This statement is used to query data from a table or view.

Syntax

simple_select:
    SELECT [ hint_options ] [ DISTINCT | UNIQUE | ALL] select_expr_list
    FROM from_list
    [WHERE condition]
    [hierarchical_query_clause]
    [GROUP BY group_expression_list
        [{ROLLUP | CUBE | GROUPING SETS} group_expression_list]
        [HAVING condition]
     ]
    [ORDER BY order_expression_list]
    [FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]
    [row_limiting_clause ]

select_expr_list:
    table_name.*
    | table_alias_name.*
    | expr [[AS] column_alias_name]
    | sequence_name.{ CURRVAL|NEXTVAL }@dblink_name

from_list:
    table_reference [, table_reference...]

table_reference:
    simple_table
    | joined_table
    | pivot_clause
    | unpivot_clause
    | table_name@dblink_name

simple_table:
    (table_factor [partition_option])[table_alias_name]
    | (select_stmt)  table_alias_name
    | (table_reference_list)

joined_table:
    table_reference [INNER] JOIN simple_table [join_condition]
    | table_reference outer_join_type JOIN simple_table join_condition

partition_option:
    PARTITION (partition_name_list)

partition_name_list:
    partition_name [, partition_name...]

outer_join_type:
    {LEFT | RIGHT | FULL} [OUTER]

join_condition:
    ON expression

condition:
    expression

group_expression_list:
    group_expression [, group_expression...]

group_expression:
    expression [ASC | DESC]

order_expression_list:
    order_expression [, order_expression...]

order_expression:
    expression [ASC | DESC]

row_limiting_clause:
    [ OFFSET offset { ROW | ROWS } ]
    [ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
        { ROW | ROWS } { ONLY | WITH TIES } ]

pivot_clause:
    PIVOT
    (aggregate_function ( expr ) [[AS] alias ]
      [, aggregate_function ( expr ) [[AS] alias ]... ]
     pivot_for_clause
     pivot_in_clause
    )

pivot_for_clause:
    FOR { column| ( column [, column... ]) }

pivot_in_clause
    IN
    ( { { expr| ( expr [, expr...] ) } [ [ AS] alias]... }
       [, { { expr| ( expr [, expr...] ) } [ [ AS] alias] ...} ]
     )

unpivot_clause:
    UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
    ( { column | ( column [, column... ]) }
     pivot_for_clause
     unpivot_in_clause
     )

unpivot_in_clause:
    IN
    ( { column | ( column [, column... ]) }[ AS { literal | ( literal [, literal... ]) } ]
        [, { column | ( column [, column... ] ) }[ AS {literal | ( literal [, literal... ]) } ]]
    )

hierarchical_query_clause:
    [START WITH start_expression] CONNECT BY [NOCYCLE] 
        {PRIOR child_expr = parent_expr 
        | parent_expr = PRIOR child_expr} [ORDER SIBLINGS BY ...]

Parameters

hierarchical_query_clause

In a hierarchical query, you can use a special pseudo column LEVEL in the SELECT statement to indicate the level, which represents the hierarchy of nodes. The level starts at 1 and increments as you move down the hierarchy. This pseudo column is only available when using hierarchical queries. For more information about pseudo columns in hierarchical queries, see Pseudo columns in hierarchical queries.

• START WITH start_expression: Optional. Specifies the root row in the hierarchical query.
• CONNECT BY: Specifies how to determine the parent-child relationship. Typically, an equality expression is used, but other expressions are also supported.
• NOCYCLE: When this keyword is specified, even if the query result contains cycles, it will still be returned. You can use the virtual column CONNECT_BY_ISCYCLE to identify where the cycles occur. If this keyword is not specified, an error will be returned to the client.
• PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr: PRIOR is a unary operator that indicates the column in the parameter comes from the parent row. It has the same precedence as the unary + and - operators.
• ORDER SIBLINGS BY: Specifies the order of rows at the same level.

For more information about using hierarchical queries, see Hierarchical queries.

Examples

• Read the name data from the tbl1 table.

  obclient> CREATE TABLE tbl1 (id INT,name VARCHAR(10),num INT);
  Query OK, 0 rows affected
  
  obclient> INSERT INTO tbl1 VALUES (1, 'a',100),(2, 'b',200),(3, 'a',50);
  Query OK, 3 rows affected
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM tbl1;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  |    2 | b    |  200 |
  |    3 | a    |   50 |
  +------+------+------+
  3 rows in set
  
  obclient> SELECT name FROM tbl1;
  +------+
  | NAME |
  +------+
  | a    |
  | b    |
  | a    |
  +------+
  3 rows in set
  

• Read the name data from the tbl1 table and perform deduplication.

  obclient> SELECT DISTINCT name FROM tbl1;
  +------+
  | NAME |
  +------+
  | a    |
  | b    |
  +------+
  2 rows in set
  

• Query the id, name, and num columns from the tbl1 table and output the num column divided by 2, with the output column named avg.

  obclient> SELECT id, name, num/2 AS avg FROM tbl1;
  +------+------+------+
  | ID   | NAME | AVG  |
  +------+------+------+
  |    1 | a    |   50 |
  |    2 | b    |  100 |
  |    3 | a    |   25 |
  +------+------+------+
  3 rows in set
  

• Query the id, name, and num columns from the tbl1 table based on the filter condition name = 'a'.

  obclient> SELECT id, name, num FROM tbl1 WHERE name = 'a';
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  |    3 | a    |   50 |
  +------+------+------+
  2 rows in set
  

• Query the name column from the tbl1 table, group the results by name, and sum the num values. Output the rows where the sum of num is less than 160.

  obclient> SELECT name,SUM(num)  sum
                  FROM tbl1
                  GROUP BY name
                  HAVING SUM(num) < 160;
  +------+------+
  | NAME | SUM  |
  +------+------+
  | a    |  150 |
  +------+------+
  1 row in set
  

• Query the id, name, and num columns from the tbl1 table and output the results in ascending order based on the num column.

  obclient> SELECT * FROM tbl1 ORDER BY num ASC;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    3 | a    |   50 |
  |    1 | a    |  100 |
  |    2 | b    |  200 |
  +------+------+------+
  3 rows in set
  

• Query all columns from the tbl1 table and output the results in descending order based on the name column and ascending order based on the num column.

  obclient> SELECT * FROM tbl1 ORDER BY name DESC,num ASC;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    2 | b    |  200 |
  |    3 | a    |   50 |
  |    1 | a    |  100 |
  +------+------+------+
  3 rows in set
  

• Query the rows with a specified id from the tbl1 table and use the FOR UPDATE clause to lock the query results.

  /* In session 1, query the row with id=1 from the tbl1 table and lock it. */
  obclient> SELECT * FROM tbl1 WHERE id=1 FOR UPDATE;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  +------+------+------+
  1 row in set
  
  /* In session 2, query the rows with id=1 or id=2 from the tbl1 table and lock them. */
  obclient> SELECT * FROM tbl1 WHERE id=1 or id=2 FOR UPDATE;
  ORA-30006: resource busy; acquire with WAIT timeout expired
  
  obclient> SELECT * FROM tbl1 WHERE id=1 or id=2 FOR UPDATE SKIP LOCKED;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    2 | b    |  200 |
  +------+------+------+
  1 row in set
  

• Create the group_tbl1 table and insert data. Execute a GROUP BY query with the CUBE option.

  obclient> CREATE TABLE group_tbl1 (group_id INT,job VARCHAR2(10),name VARCHAR2(10),salary INT);
  Query OK, 0 rows affected
  obclient> INSERT INTO group_tbl1 VALUES(10,'Coding','Bruce',1000),
      (10,'Programmer','Clair',1000),
      (20,'Coding','Jason',2000),
      (20,'Programmer','Joey',2000),
      (30,'Coding','Rebecca',3000),
      (30,'Programmer','Rex',3000);
  Query OK, 6 rows affected
  Records: 6  Duplicates: 0  Warnings: 0
   obclient> SELECT * FROM group_tbl1;
   +----------+------------+---------+--------+
   | GROUP_ID | JOB        | NAME    | SALARY |
   +----------+------------+---------+--------+
   |       10 | Coding     | Bruce   |   1000 |
   |       10 | Programmer | Clair   |   1000 |
   |       20 | Coding     | Jason   |   2000 |
   |       20 | Programmer | Joey    |   2000 |
   |       30 | Coding     | Rebecca |   3000 |
   |       30 | Programmer | Rex     |   3000 |
   +----------+------------+---------+--------+
   6 rows in set
   obclient> SELECT group_id, salary, SUM(salary) FROM group_tbl1 GROUP BY CUBE (group_id, salary);
   +----------+--------+-------------+
   | GROUP_ID | SALARY | SUM(SALARY) |
   +----------+--------+-------------+
   |     NULL |   NULL |       12000 |
   |     NULL |   1000 |        2000 |
   |     NULL |   2000 |        4000 |
   |     NULL |   3000 |        6000 |
   |       10 |   NULL |        2000 |
   |       20 |   NULL |        4000 |
   |       30 |   NULL |        6000 |
   |       10 |   1000 |        2000 |
   |       20 |   2000 |        4000 |
   |       30 |   3000 |        6000 |
   +----------+--------+-------------+
   10 rows in set
  

• Group the query results by the name and num columns and count the number of rows in each group from the tbl1 table.

  obclient> SELECT name, num, COUNT(*) from tbl1 GROUP BY GROUPING SETS(name, num);
  +------+------+----------+
  | NAME | NUM  | COUNT(*) |
  +------+------+----------+
  | a    | NULL |        2 |
  | b    | NULL |        1 |
  | NULL |  100 |        1 |
  | NULL |  200 |        1 |
  | NULL |   50 |        1 |
  +------+------+----------+
  5 rows in set
  

• Convert the rows in the emp_phone table into columns and then convert the columns back into rows.

  obclient> CREATE TABLE emp(name VARCHAR2(50), num CHAR, phone VARCHAR2(50));
  Query OK, 0 rows affected
  
  obclient> INSERT INTO emp VALUES('ZhangSan', '1', '1234-5678'),('ZhangSan', '2', '3219-6066'),('ZhangSan', '3', '5365-9583');
  Query OK, 3 rows affected
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM emp;
  +----------+------+-----------+
  | NAME     | NUM  | PHONE     |
  +----------+------+-----------+
  | ZhangSan | 1    | 1234-5678 |
  | ZhangSan | 2    | 3219-6066 |
  | ZhangSan | 3    | 5365-9583 |
  +----------+------+-----------+
  3 rows in set
  
  /* Convert the rows in the emp table into columns. */
  obclient> SELECT * FROM emp PIVOT(MAX(phone) FOR num IN (1 AS home, 2 AS office, 3 AS mobile));
  +----------+-----------+-----------+-----------+
  | NAME     | HOME      | OFFICE    | MOBILE    |
  +----------+-----------+-----------+-----------+
  | ZhangSan | 1234-5678 | 3219-6066 | 5365-9583 |
  +----------+-----------+-----------+-----------+
  1 row in set
  
  /* Convert the columns in the emp table into rows. */
  obclient> CREATE VIEW v_emp AS SELECT * FROM emp PIVOT(MAX(phone) FOR num IN (1 AS home, 2 AS office, 3 AS mobile));
  Query OK, 0 rows affected
  
  obclient>  SELECT * FROM v_emp UNPIVOT(phone FOR num IN (home AS 1, office AS 2, mobile AS 3));
  +----------+-----+-----------+
  | NAME     | NUM | PHONE     |
  +----------+-----+-----------+
  | ZhangSan |   1 | 1234-5678 |
  | ZhangSan |   2 | 3219-6066 |
  | ZhangSan |   3 | 5365-9583 |
  +----------+-----+-----------+
  3 rows in set
  

• Query data from a table in a remote database.

  /* Access the OceanBase database in the remote database to query data. */
  obclient> SELECT ID FROM tbl2@ob_dblink;
  +------+
  | ID   |
  +------+
  |    1 |
  |    2 |
  |    3 |
  +------+
  3 rows in set
  
  obclient> SELECT * FROM tbl2@ob_dblink;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  |    2 | b    |  200 |
  |    3 | a    |   50 |
  +------+------+------+
  3 rows in set
  
  /*Query data from the remote Oracle database.*/
  obclient> SELECT ID FROM tbl2@ora_dblink;
  +------+
  | ID   |
  +------+
  |    1 |
  |    2 |
  |    3 |
  +------+
  3 rows in set
  
  obclient> SELECT * FROM tbl2@ora_dblink;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  |    2 | b    |  200 |
  |    3 | a    |   50 |
  +------+------+------+
  3 rows in set
  
  /*Query data from the local database and the remote database at the same time.*/
  obclient> SELECT t4.col1,t5.col2 FROM tbl1 t4, tbl2@ob_dblink t5 WHERE t1.col3=t2.col3;
  
  /*Query data from different remote databases at the same time.*/
  obclient> SELECT * FROM tbl2@ob_dblink t_remote1,tbl2@ora_dblink t_remote2 WHERE t_remote1.col1 = t_remote2.col1;
  

• Example of a pagination query

  • Query the employee numbers of the three employees with the lowest salaries.

    obclient> CREATE TABLE emp(  
        empno         NUMBER(4,0),  
        empname       VARCHAR(10),  
        job           VARCHAR(9),  
        mgr           NUMBER(4,0),  
        hiredate      DATE,  
        sal           NUMBER(7,2),  
        comm          NUMBER(7,2),        
        deptno        NUMBER(2,0),   
        CONSTRAINT PK_emp PRIMARY KEY (empno)
    );
    Query OK, 0 rows affected
    
    obclient> INSERT INTO emp VALUES (1839,'KING','PRESIDENT',null,'17-DEC-81',5000,null,10)
       ,(1698,'BLAKE','MANAGER',1839,'01-MAY-81',2850,null,30)
       ,(1782,'CLARK', 'MANAGER', 1839, '09-JUN-81', 2450, null, 10)
       ,(1566,'JONES','MANAGER',1839, '02-APR-81',2975,null,40)
       ,(1788,'SCOTT','ANALYST',1566, '15-JUL-87',3000,null,20)
       ,(1902,'FORD','ANALYST',1566, '05-OCT-81',3000,null,40)
       ,(1369,'SMITH','CLERK',1902, '17-NOV-80',800,null,20)
       ,(1499,'ALLEN','SALESMAN',1698, '20-FEB-81',1600,300,30);
    
    Query OK, 8 rows affected
    Records: 8  Duplicates: 0  Warnings: 0
    
    obclient> SELECT empno, empname FROM emp ORDER BY empno FETCH FIRST 3 ROWS ONLY;
    +-------+---------+
    | EMPNO | EMPNAME |
    +-------+---------+
    |  1369 | SMITH   |
    |  1499 | ALLEN   |
    |  1566 | JONES   |
    +-------+---------+
    3 rows in set
    

  • Query the employee numbers of the three employees with the lowest salaries again.

    obclient> SELECT empno, empname
    FROM emp
    ORDER BY empno
    FETCH NEXT 3 ROWS ONLY;
    

  • Query the employees with the lowest salaries in the first 25% of the employees.

    obclient> SELECT empno, empname sal FROM emp ORDER BY sal
     FETCH FIRST 25 PERCENT ROWS ONLY;
    +-------+-------+
    | EMPNO | SAL   |
    +-------+-------+
    |  1499 | ALLEN |
    |  1698 | BLAKE |
    +-------+-------+
    2 rows in set
    

  • Query the employees with the lowest salaries in the first 25% of the employees, and all other employees whose salaries are the same as the last employee in the previous example.

    obclient> SELECT empno, empname sal FROM emp ORDER BY sal
     FETCH FIRST 25 PERCENT ROWS WITH TIES;
    +-------+-------+
    | EMPNO | SAL   |
    +-------+-------+
    |  1499 | ALLEN |
    |  1698 | BLAKE |
    +-------+-------+
    2 rows in set
    

• Query data from the t1 table and specify an alias for the table.

  obclient> CREATE TABLE t1 (c1 INT, c2 INT );
  Query OK, 0 rows affected
  
  obclient> INSERT INTO t1 VALUES ('1','2');
  Query OK, 1 row affected
  
  obclient> SELECT * FROM (t1) a;
  +------+------+
  | C1   | C2   |
  +------+------+
  |    1 |    2 |
  +------+------+
  1 row in set
  

• Use the RANDOM() function to generate a random number and directly query the GENERATOR() function in the FROM clause.

  obclient> SELECT RANDOM(4) FROM GENERATOR(3);
  +---------------------+
  | RANDOM(4)           |
  +---------------------+
  | 5267436225003336391 |
  | -851690886662571060 |
  | 1738617244330437274 |
  +---------------------+
  3 rows in set
  

• Access the sequence value in the remote database.

  1.Log in to the local OceanBase Database and create a dblink to the Oracle tenant of the remote OceanBase Database.

  obclient> CREATE DATABASE LINK seq_link CONNECT TO test@oracle IDENTIFIED BY test HOST '127.xxx.xxx.xxx:2828';
  Query OK, 0 rows affected
  

  2.Create a sequence named my_seq in the remote OceanBase Database.

  obclient> CREATE SEQUENCE my_seq START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;
  Query OK, 0 rows affected
  

  3.Access the sequence value in the remote OceanBase Database from the local OceanBase Database.

  obclient> SELECT my_seq.NEXTVAL@seq_link FROM DUAL;
  +---------+
  | NEXTVAL |
  +---------+
  |       1 |
  +---------+
  1 row in set
  
  obclient> SELECT my_seq.CURRVAL@seq_link FROM DUAL;
  +---------+
  | CURRVAL |
  +---------+
  |       1 |
  +---------+
  1 row in set
  
  obclient> SELECT my_seq.NEXTVAL@seq_link FROM DUAL;
  +---------+
  | NEXTVAL |
  +---------+
  |       3 |
  +---------+
  1 row in set