SIMPLE SELECT

The syntax of the SELECT statement is complex. This topic describes the SIMPLE SELECT syntax.

Description

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]
    [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... ]) } ]]
    )

Parameters

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 remove the duplicates.

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

• Query the id, name, and num columns from the tbl1 table. Then divide the values in the num column by 2 and output the results with the column name 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
  

• Return the values in the id, name, and num columns from the tbl1 table that meet 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 data by the name column and sum the values in the num column. Return only the rows where the sum 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. Sort the query results in ascending order by the values in 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. Sort the query results in descending order by the values in the name column and in ascending order by the values in 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 that have the specified id from the tbl1 table and lock the query results with the FOR UPDATE clause.

  /* Query and lock the row with the ID of 1 from the tbl1 table in session 1 */
  obclient> SELECT * FROM tbl1 WHERE id=1 FOR UPDATE;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  +------+------+------+
  1 row in set
  
  /* Query and lock the rows with the IDs of 1 and 2 from the tbl1 table in session 2 */
  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 a table named group_tbl1 and insert data. Execute a GROUP BY query statement with the CUBE operator.

  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
  

• Query data from the tbl1 table by grouping the data in the table by the name and num columns and counting the number of rows in each group.

  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 back 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.

  /*Query data from a table in OceanBase Database*/
  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 a table in 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 both a local database and a remote database*/
  obclient> SELECT t4.col1,t5.col2 FROM tbl1 t4, tbl2@ob_dblink t5 WHERE t1.col3=t2.col3;
  
  /*Query data from tables in different remote databases*/
  obclient> SELECT * FROM tbl2@ob_dblink t_remote1,tbl2@ora_dblink t_remote2 WHERE t_remote1.col1 = t_remote2.col1;
  

• Paginate query results

  • Query the employees with the three lowest empno values.

    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 employees with the next three lowest empno values.

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

  • Query the top 25% of employees with the lowest salary values.

    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 top 25% of employees with the lowest salary values and all other employees with the same salary value as the last employee obtained 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 a table alias.

  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
  

• Generate a random number by using the RANDOM() function and query the GENERATOR() function directly after the FROM clause.

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

• Access the sequence values in a remote database.

  1. Log in to the local OceanBase database and create a dblink to an Oracle tenant of 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. In the remote OceanBase database, create a sequence named my_seq.

  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. In the local database, access the sequence values in the remote 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