Query a single table|V4.3.5|OceanBase Database| docs|Distributed Database

This topic describes how to query a single table in OceanBase Database by using SQL statements.

Prerequisites

You have connected to an Oracle tenant of OceanBase Database. For more information about how to connect to the database, see Overview of connection methods.
You have the SELECT privilege. For more information about how to view the privileges of the current user, see View user privileges. If you do not have this privilege, contact the administrator to grant it to you. For more information about how to grant privileges to users, see Directly grant privileges.

Syntax

Use the SELECT statement to query data.

The general structure of a single-table query using the SELECT statement is as follows:

SELECT [ALL | DISTINCT] select_list FROM table_name
              [ WHERE query_condition ]
              [ GROUP BY group_by_expression ]
              [ HAVING group_condition ]
              [ ORDER BY column_list ][ASC | DESC]
              [ Row_Limiting_Clause ]

column_list：
 column_name[,column_name...]

Parameters:

Parameter	Description
select_list	A list of columns, expressions, or aggregate functions to be retrieved. Multiple columns can be separated with commas.
table_name	The name of the table from which to retrieve data.
WHERE query_condition	Optional. The condition for querying data. Only rows that meet the condition are returned.
GROUP BY group_by_condition	Optional. The column by which to group the result set. This parameter is usually used together with aggregate functions.
HAVING group_condition	Optional. The condition for filtering the grouped result set. Only groups that meet the condition are returned.
ORDER BY column_list	Optional. The column or columns by which to sort the result set. One or more columns can be specified.
ASC	DESC	Optional. The sorting order. ASC indicates ascending order (the default), and DESC indicates descending order.
LIMIT limit_clause	Optional. The number or percentage of rows to return in the query result set for paging queries. The offset can also be specified. The `ORDER BY` clause can be combined with this parameter to ensure the sorting order and obtain consistent results.
column_list	The column or columns to be retrieved. Multiple columns must be separated with commas.
column_name	The name of the column to be retrieved.

Execution order of SELECT keywords

When the WHERE, GROUP BY, HAVING, and ORDER BY keywords are used together, there are clear restrictions on their order of execution. The keywords are executed in the following order:

The FROM keyword is executed to locate the table.
The WHERE keyword is executed to specify the constraints.
The GROUP BY keyword is executed to group each record. If the GROUP BY keyword is not used, all records are grouped as one.
The HAVING keyword is executed to filter the grouped results.
The SELECT keyword is executed.
The DISTINCT keyword is executed to remove duplicates.
The ORDER BY keyword is executed to sort the results in ascending or descending order.
The ROWNUM keyword is executed to limit the number of rows to be displayed in the result set.

Notice

The difference between WHERE and HAVING is that WHERE is used to filter data before grouping, while HAVING is used to filter the grouped results. The result set of the entire SQL statement is returned.

Create test tables and add test data

Create the dept table.

CREATE TABLE dept(  
    deptno           NUMBER(2,0),  
    dname            VARCHAR(14),  
    location         VARCHAR(13),   
    CONSTRAINT pk_dept PRIMARY KEY(deptno)  
);

Insert six records into the dept table.

INSERT INTO dept VALUES
(20,'Finance','beijing'),
(35,'Administration','hangzhou'),
(40,'Development','xian'),    
(30,'Workshop','guangzhou'),    
(25,'Legal affairs','shanghai'),    
(45,'Office','suzhou');

Create the emp table.

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),
    age           NUMBER(2,0),
    CONSTRAINT PK_emp PRIMARY KEY (empno),
    CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)  
);

Insert six records into the emp table.

INSERT INTO emp VALUES
(1369,'SMITH','CLERK',1902,DATE'1980-12-17',800.00,NULL,20,22),
(1499,'ALLEN','SALESMAN',1698,DATE'1981-02-20',1600.00,300.00,35,22),
(1566,'JONES','MANAGER',1839,DATE'1981-04-02',2975.00, NULL,40,22),    
(1698,'BLAKE' ,'MANAGER',1839,DATE'1981-05-01',2850.00,NULL ,30,33),    
(1788,'SCOTT','ANALYST',1566,DATE'1987-07-15',3000.00,NULL ,25,33),    
(1902,'FORD','ANALYST',1566,DATE'1981-12-05',3000.00, NULL,45,22);

Basic queries

Query all columns

* indicates to return all fields of the table. Example:

obclient [SYS]> SELECT * FROM emp;

Return result:

+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK    | 1902 | 17-DEC-80 |  800 | NULL |     20 |   22 |
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
|  1566 | JONES   | MANAGER  | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER  | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
|  1788 | SCOTT   | ANALYST  | 1566 | 15-JUL-87 | 3000 | NULL |     25 |   33 |
|  1902 | FORD    | ANALYST  | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
6 rows in set

Equivalent to:

obclient [SYS]> SELECT empname,empno,job,mgr,hiredate,sal,comm,deptno FROM emp;

Return result:

+---------+-------+----------+------+-----------+------+------+--------+
| EMPNAME | EMPNO | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO |
+---------+-------+----------+------+-----------+------+------+--------+
| SMITH   |  1369 | CLERK    | 1902 | 17-DEC-80 |  800 | NULL |     20 |
| ALLEN   |  1499 | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |
| JONES   |  1566 | MANAGER  | 1839 | 02-APR-81 | 2975 | NULL |     40 |
| BLAKE   |  1698 | MANAGER  | 1839 | 01-MAY-81 | 2850 | NULL |     30 |
| SCOTT   |  1788 | ANALYST  | 1566 | 15-JUL-87 | 3000 | NULL |     25 |
| FORD    |  1902 | ANALYST  | 1566 | 05-DEC-81 | 3000 | NULL |     45 |
+---------+-------+----------+------+-----------+------+------+--------+
6 rows in set

Specify a table alias during the query

Query data of the emp table and specify the table alias as t.

obclient [SYS]> SELECT t.* FROM (emp) t;

Return result:

+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK    | 1902 | 17-DEC-80 |  800 | NULL |     20 |   22 |
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
|  1566 | JONES   | MANAGER  | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER  | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
|  1788 | SCOTT   | ANALYST  | 1566 | 15-JUL-87 | 3000 | NULL |     25 |   33 |
|  1902 | FORD    | ANALYST  | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
6 rows in set

Query specified columns

obclient [SYS]> SELECT empname,deptno FROM emp;

Return result:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| BLAKE   |     30 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
6 rows in set

Set column aliases during the query

obclient [SYS]> SELECT empname AS Employee_Name, deptno AS Department_Number FROM emp;

Return result:

+--------------+--------------+
| Employee Name | Department ID |
+--------------+--------------+
| SMITH        |           20 |
| ALLEN        |           35 |
| JONES        |           40 |
| BLAKE        |           30 |
| SCOTT        |           25 |
| FORD         |           45 |
+--------------+--------------+
6 rows in set

Eliminate rows with duplicate values during the query

obclient [SYS]> SELECT DISTINCT age FROM emp;

Return result:

+------+
| AGE  |
+------+
|   22 |
|   33 |
+------+
2 rows in set

Use `ROWNUM` to limit the number of returned rows during the query

obclient [SYS]> SELECT empname, deptno FROM emp WHERE ROWNUM <= 3;

Return result:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
+---------+--------+
3 rows in set

Query calculated values

obclient [SYS]> SELECT empname, sal-100,job FROM emp;

Return result:

+---------+---------+----------+
| EMPNAME | SAL-100 | JOB      |
+---------+---------+----------+
| SMITH   |     700 | CLERK    |
| ALLEN   |    1500 | SALESMAN |
| JONES   |    2875 | MANAGER  |
| BLAKE   |    2750 | MANAGER  |
| SCOTT   |    2900 | ANALYST  |
| FORD    |    2900 | ANALYST  |
+---------+---------+----------+
6 rows in set

You can use a function for a specified column in a query. In the following example, the job column is displayed in lowercase. For more information, see Use operators and functions in a query.

obclient [SYS]> SELECT empname, sal-100, LOWER(job) FROM emp;

Return result:

+---------+---------+------------+
| EMPNAME | SAL-100 | LOWER(JOB) |
+---------+---------+------------+
| SMITH   |     700 | clerk      |
| ALLEN   |    1500 | salesman   |
| JONES   |    2875 | manager    |
| BLAKE   |    2750 | manager    |
| SCOTT   |    2900 | analyst    |
| FORD    |    2900 | analyst    |
+---------+---------+------------+
6 rows in set

Query results of a function

In the Oracle mode of OceanBase Database, you can call a function that returns a table type by using the SELECT * FROM FUNCTION(*); syntax, and query the function's return results as table data.

Take note of the following considerations when you use the SELECT * FROM FUNCTION(*); syntax:

The data type of the function's return value must be a table type.
The number and type of columns in the table returned by the table function must match the number and type of columns required by the caller.
The number of rows in the table returned by the table function must be greater than or equal to the number of rows required by the caller.
The number and type of the function's parameters must match the definition of the function.

For more information about custom data types, see CREATE TYPE and User-defined subtypes.

For more information about how to create a function, see CREATE FUNCTION and Create a function.

Here is an example:

Define the emp_type table type.

obclient [SYS]> DELIMITER //
obclient [SYS]> CREATE OR REPLACE TYPE emp_type AS OBJECT (id NUMBER); //
Query OK, 0 rows affected

Define the emp_type_list collection type that contains the emp_type table type.

obclient [SYS]> CREATE OR REPLACE TYPE emp_type_list IS TABLE OF emp_type; //
Query OK, 0 rows affected

Create a get_emp_info function that returns a table type.

obclient [SYS]> CREATE OR REPLACE FUNCTION get_emp_info
        RETURN emp_type_list PIPELINED
    IS CURSOR emp_list_cursor IS SELECT EMPNO FROM emp;
        v_emp_id_type emp_type;
      v_emp_id varchar2(5);
      BEGIN
      OPEN emp_list_cursor;
      LOOP
      FETCH emp_list_cursor INTO v_emp_id;
      EXIT WHEN emp_list_cursor%notfound;
      v_emp_id_type := emp_type(v_emp_id);
      PIPE ROW(v_emp_id_type);
      END LOOP;
      CLOSE emp_list_cursor;
      RETURN;
      END;//
Query OK, 0 rows affected

obclient [SYS]> DELIMITER ;

Query the return results of the function.

obclient [SYS]> SELECT * FROM get_emp_info();

The return result is as follows:

+------+
| ID   |
+------+
| 1369 |
| 1499 |
| 1566 |
| 1698 |
| 1788 |
| 1902 |
+------+
6 rows in set

Data filtering

To query data that meets specific conditions, you can add a WHERE clause to the SELECT query statement. The WHERE clause can contain one or more conditions for filtering data. Only data that meets the WHERE conditions is returned. You can flexibly use query conditions based on your needs to filter and retrieve target data.

When using the WHERE clause, make sure that the conditions are correct and appropriate operators are used.

The following table describes common query conditions in the WHERE clause.

Query condition type	Predicate
Comparative query	=, >, <, >=, <=, !=, <>
Logical query (multiple conditions)	AND, OR, NOT
Fuzzy query (character matching)	LIKE, NOT LIKE
Range query (specified range)	BETWEEN AND, NOT BETWEEN AND
Set query	IN, NOT IN
NULL value query	IS NULL, IS NOT NULL

Compare conditions

Equal to (=)

Queries data that is equal to the specified value in the column. If the value is a string, enclose it in single or double quotation marks.

obclient [SYS]> SELECT empname, deptno FROM emp WHERE deptno = 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| BLAKE   |     30 |
+---------+--------+
1 row in set

obclient [SYS]> SELECT empname, deptno FROM emp WHERE empname = 'ALLEN';

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

Not equal to (<> and !=)

Not equal to is written in two forms: <> and !=. The general format is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE column_name <> const_value;
SELECT column_name [,column_name...] FROM table_name WHERE column_name != const_value;

Here is an example:

obclient> SELECT empname, deptno FROM emp WHERE deptno <> 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
5 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno != 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
5 rows in set

Greater than (>) and less than (<)

The greater than (>) and less than (<) operators compare numeric values. For characters, the ASCII values of characters are compared. The comparison is performed in sequence by the positions of characters. The general format is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE column_name < const_value;
SELECT column_name [,column_name...] FROM table_name WHERE column_name > const_value;

Note

The greater than or equal to (>=) and less than or equal to (<=) operators are similar to the preceding two operators.

Here are some examples:

obclient> SELECT empname, deptno FROM emp WHERE deptno > 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
| JONES   |     40 |
| FORD    |     45 |
+---------+--------+
3 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno >= 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
| JONES   |     40 |
| BLAKE   |     30 |
| FORD    |     45 |
+---------+--------+
4 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno < 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| SCOTT   |     25 |
+---------+--------+
2 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno <= 30;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| BLAKE   |     30 |
| SCOTT   |     25 |
+---------+--------+
3 rows in set

Logical conditions

The logical query operators AND and OR support queries that contain multiple conditions.

AND

The AND operator returns data that meets both conditions. The general format is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE
query_condition AND query_condition;

Here is an example:

obclient> SELECT empname, deptno, sal FROM emp WHERE deptno<=30 AND sal > 1000;

The return result is as follows:

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| BLAKE   |     30 | 2850 |
| SCOTT   |     25 | 3000 |
+---------+--------+------+
2 rows in set

OR

The OR operator returns data that meets any one of the conditions. The general format is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE
query_condition OR query_condition;

Here is an example:

obclient> SELECT empname, deptno, sal FROM emp WHERE deptno <= 30 OR sal > 1000;

The return result is as follows:

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| SMITH   |     20 |  800 |
| ALLEN   |     35 | 1600 |
| JONES   |     40 | 2975 |
| BLAKE   |     30 | 2850 |
| SCOTT   |     25 | 3000 |
| FORD    |     45 | 3000 |
+---------+--------+------+
6 rows in set

Fuzzy queries (LIKE)

The LIKE predicate can be used for string matching. The general format is as follows:

[NOT] LIKE pattern

The syntax means to find tuples whose attribute values match the pattern. pattern can be a complete string or a string that contains the wildcard characters % and _. Here are the meanings of the wildcard characters:

An underscore _ matches any single character.
A percent sign % matches zero or more characters in the value. The pattern % cannot match NULL.

Note

In the Oracle mode of OceanBase Database, the following conditions must be met for the system to convert like to =:

pattern does not contain the % or _ wildcard, and there is no escape.
The field type is not LOB. The LOB type is not supported in the Oracle mode, and therefore cannot be converted.
pattern is not a fixed-length char or nchar type.

Example: Query data where the first four letters of the employee name are ALLE and the last letter is any character.

obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'ALLE_';

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

Example: Query data where the first letter of the employee name is A.

obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'A%';

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

Note

When the database character set is ASCII, two _ characters are needed to match one Chinese character; when the character set is GBK, only one _ character is needed.

Range queries (BETWEEN AND)

The BETWEEN ... AND operator selects data that falls within a range defined by two values. These values can be numeric, text, or date values. The general format is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE
[NOT] BETWEEN min_const_value AND max_const_value;

Note

Do not reverse the two boundary values in a range query. The left boundary value must be less than or equal to the right boundary value.

Example:

obclient> SELECT * FROM emp WHERE sal BETWEEN 2000 AND 2999;

The return result is as follows:

+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
+-------+---------+---------+------+-----------+------+------+--------+------+
2 rows in set

Specify multiple values (IN)

The IN operator allows you to specify multiple values in the WHERE clause. These values can be treated as a set. The IN operator returns rows where the specified column values match any of the values in the set. The NOT IN operator returns rows where the specified column values do not match any of the values in the set. The general format is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE
column_name [NOT] IN (const_value,const_value,const_value...);

Notice

The values in the [NOT] IN set must be of the same type or mutually compatible.
Values in the [NOT] IN set are not supported.

Here is an example:

obclient> SELECT * FROM emp WHERE deptno IN (30,40,50,60);

The return result is as follows:

+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
+-------+---------+---------+------+-----------+------+------+--------+------+
2 rows in set

Queries that use NULL values (IS NULL/IS NOT NULL)

We recommend that you use the IS NULL or IS NOT NULL statement to query NULL values because the query results of other comparison operators, such as LIKE, BETWEEN AND, IN, and NOT IN, are inaccurate for NULL values.

IS NULL

Use the IS NULL statement to query data where the specified column values are NULL. The general format is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE
column_name IS NULL;

Here is an example:

obclient> SELECT * FROM emp WHERE comm IS NULL;

The return result is as follows:

+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK   | 1902 | 17-DEC-80 |  800 | NULL |     20 |   22 |
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
|  1788 | SCOTT   | ANALYST | 1566 | 15-JUL-87 | 3000 | NULL |     25 |   33 |
|  1902 | FORD    | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
+-------+---------+---------+------+-----------+------+------+--------+------+
5 rows in set

IS NOT NULL

Use the IS NOT NULL statement to query data where the specified column values are not NULL. The general format is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE
column_name IS NOT NULL;

Here is an example:

obclient> SELECT * FROM emp WHERE comm IS NOT NULL;

The return result is as follows:

+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
1 row in set

Group queries

The ORDER BY clause is used to sort the query result by one or more attribute columns in ascending (ASC) or descending (DESC) order. The default order is ascending.

GROUP BY supports single-field grouping and multi-field grouping. You can use the WHERE clause to filter data before grouping, the HAVING clause to filter data after grouping, and the ORDER BY clause to sort data after grouping.

The following table describes commonly used aggregate functions in grouping.

Aggregate function	Description
MAX()	Queries the maximum value in a specified column.
MIN()	Queries the minimum value in a specified column.
COUNT()	Counts the number of rows in the query result.
SUM()	Returns the sum of a specified column.
AVG()	Returns the average value of data in a specified column.

Group query examples

Create the fruit_order table.

CREATE TABLE fruit_order(
  order_id Number(10,2),
  user_id Number(10,2),
  user_name VARCHAR2(16),
  fruit_price Number(10,2),
  order_year Date,
  PRIMARY KEY (order_id)
);

Insert some test data.

INSERT INTO fruit_order(order_id,user_id,user_name,fruit_price,order_year) VALUES
  (1,1011,'Zhang San',13.11,Date'2019-01-01'),
  (4,1011,'Zhang San',22.21,Date'2020-01-01'),
  (6,1011,'Zhang San',58.83,Date'2020-02-02'),
  (2,1022,'Li Si',23.34,Date'2019-02-02'),
  (3,1022,'Li Si',12.22,Date'2019-03-03'),
  (7,1022,'Li Si',14.66,Date'2021-03-03'),
  (8,1022,'Li Si',34.44,Date'2021-04-04'),
  (5,1033,'Wang Wu',51.55,Date'2020-05-05'),
  (9,1033,'Wang Wu',63.66,Date'2021-06-06');

Single-field grouping query

Query the order quantity of each customer and output the customer ID and order quantity.

obclient [SYS]> SELECT user_id AS CustomerID, COUNT(order_id) AS OrderCount FROM fruit_order GROUP BY user_id;

The return result is as follows:

+----------+--------------+
| Customer ID | Order Quantity |
+----------+--------------+
|     1011 |            3 |
|     1022 |            4 |
|     1033 |            2 |
+----------+--------------+
3 rows in set

Multi-field grouping query

Query the order quantity of each customer in each year and output the customer ID, year of the order, and order quantity.

obclient [SYS]> SELECT user_id AS CustomerID, order_year AS OrderYear, COUNT(order_id) AS OrderCount FROM fruit_order GROUP BY user_id, order_year;

The return result is as follows:

+----------+--------------+--------------+
| Customer ID | Order Year | Order Quantity |
+----------+--------------+--------------+
|     1011 | 01-JAN-19    |            1 |
|     1022 | 02-FEB-19    |            1 |
|     1022 | 03-MAR-19    |            1 |
|     1011 | 01-JAN-20    |            1 |
|     1033 | 05-MAY-20    |            1 |
|     1011 | 02-FEB-20    |            1 |
|     1022 | 03-MAR-21    |            1 |
|     1022 | 04-APR-21    |            1 |
|     1033 | 06-JUN-21    |            1 |
+----------+--------------+--------------+
9 rows in set

Filter before grouping

Query the order quantity of each customer in 2020 and output the customer ID and order quantity.

obclient [SYS]> SELECT user_id AS CustomerID, COUNT(order_id) AS OrderCount FROM fruit_order t WHERE t.order_year = '01-JAN-20' GROUP BY user_id;

The return result is as follows:

+----------+--------------+
| Customer ID | Order Quantity |
+----------+--------------+
|     1011 |            1 |
+----------+--------------+
1 row in set

Filter after grouping

Query the customers who placed at least one order in 2019 and output the customer ID and order quantity.

obclient [SYS]> SELECT user_id AS CustomerID, COUNT(order_id) AS OrderCount FROM fruit_order t WHERE t.order_year = '01-JAN-19' GROUP BY user_id HAVING COUNT(order_id) >= 1;

The return result is as follows:

+----------+--------------+
| Customer ID | Order Quantity |
+----------+--------------+
|     1011 |            1 |
+----------+--------------+
1 row in set

Group by and sort

Query the maximum amount of each customer's orders, and then output the customer ID and the maximum amount in descending order.

obclient [SYS]> SELECT user_id AS CustomerID, MAX(fruit_price) AS MaxAmount FROM fruit_order t GROUP BY user_id ORDER BY MaxAmount DESC;

The return result is as follows:

+----------+--------------+
| Customer ID | Maximum Amount |
+----------+--------------+
|     1033 |        63.66 |
|     1011 |        58.83 |
|     1022 |        34.44 |
+----------+--------------+
3 rows in set

Group by and aggregate

Use the GROUP BY CUBE clause to group the fruit_order table by user_id and aggregate the grouping result.

obclient [SYS]> SELECT user_id,SUM(FRUIT_PRICE) FROM fruit_order GROUP BY CUBE(user_id);

The return result is as follows:

+---------+------------------+
| USER_ID | SUM(FRUIT_PRICE) |
+---------+------------------+
|    NULL |           294.02 |
|    1011 |            94.15 |
|    1022 |            84.66 |
|    1033 |           115.21 |
+---------+------------------+
4 rows in set

For more information about grouping and aggregation, see SIMPLE SELECT.

Sorting queries

The ORDER BY clause sorts the query result set by one or more columns in ascending (ASC) or descending (DESC) order. The default order is ascending.

Query customers who placed orders in 2019 and output their customer IDs and order quantities.

obclient [SYS]> SELECT user_id,USER_NAME,SUM(FRUIT_PRICE) FROM fruit_order GROUP BY CUBE(user_id,USER_NAME);

The return result is as follows:

+---------+-----------+------------------+
| USER_ID | USER_NAME | SUM(FRUIT_PRICE) |
+---------+-----------+------------------+
|    NULL | NULL      |           294.02 |
|    NULL | Zhang San |            94.15 |
|    NULL | Li Si      |            84.66 |
|    NULL | Wang Wu      |           115.21 |
|    1011 | NULL      |            94.15 |
|    1022 | NULL      |            84.66 |
|    1033 | NULL      |           115.21 |
|    1011 | Zhang San |            94.15 |
|    1022 | Lisi      |            84.66 |
|    1033 | Wang Wu   |           115.21 |
+---------+-----------+------------------+
10 rows in set

Sort by a single field

Display employee information sorted by `deptno` in ascending order

obclient [SYS]> SELECT empname, deptno FROM emp ORDER BY deptno;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| SCOTT   |     25 |
| BLAKE   |     30 |
| ALLEN   |     35 |
| JONES   |     40 |
| FORD    |     45 |
+---------+--------+
6 rows in set

Display employee information sorted by `deptno` in descending order

obclient [SYS]> SELECT empname, deptno FROM emp ORDER BY deptno DESC;

The return result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| FORD    |     45 |
| JONES   |     40 |
| ALLEN   |     35 |
| BLAKE   |     30 |
| SCOTT   |     25 |
| SMITH   |     20 |
+---------+--------+
6 rows in set

Sort by multiple fields

Display employee information sorted by deptno in ascending order and sal in descending order.

obclient [SYS]> SELECT empname, deptno, sal FROM emp ORDER BY deptno ASC,sal DESC;

The return result is as follows:

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| SMITH   |     20 |  800 |
| SCOTT   |     25 | 3000 |
| BLAKE   |     30 | 2850 |
| ALLEN   |     35 | 1600 |
| JONES   |     40 | 2975 |
| FORD    |     45 | 3000 |
+---------+--------+------+
6 rows in set

Sort after WHERE

ORDER BY can be used after WHERE. Example:

obclient [SYS]> SELECT empname, deptno,sal, HIREDATE FROM emp WHERE sal>=1000 ORDER BY HIREDATE;

The return result is as follows:

+---------+--------+------+-----------+
| EMPNAME | DEPTNO | SAL  | HIREDATE  |
+---------+--------+------+-----------+
| ALLEN   |     35 | 1600 | 20-FEB-81 |
| JONES   |     40 | 2975 | 02-APR-81 |
| BLAKE   |     30 | 2850 | 01-MAY-81 |
| FORD    |     45 | 3000 | 05-DEC-81 |
| SCOTT   |     25 | 3000 | 15-JUL-87 |
+---------+--------+------+-----------+
5 rows in set

Sort using NLSSORT

You can use the NLSSORT function to sort Chinese characters by Pinyin or stroke.

For more information about the NLSSORT function, see NLSSORT.

Sort by Pinyin

obclient [SYS]> SELECT empname, deptno,sal FROM emp ORDER BY NLSSORT(empname,'NLS_SORT=UCA0900_SCHINESE_PINYIN');

The return result is as follows:

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| ALLEN   |     35 | 1600 |
| BLAKE   |     30 | 2850 |
| FORD    |     45 | 3000 |
| JONES   |     40 | 2975 |
| SCOTT   |     25 | 3000 |
| SMITH   |     20 |  800 |
+---------+--------+------+
6 rows in set

Use Row_Limiting_Clause in queries

Row_Limiting_Clause is used to limit the number of rows returned by SELECT queries. It is commonly used for pagination.

Query data after the Nth row

Example 1

Query data after the 4th row.

obclient> SELECT * FROM tb OFFSET 4 ROW;

The return result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    5 | b    |  700 |
|    6 | a    |   80 |
+------+------+------+
2 rows in set

Example 2

Query data after the 5th row.

obclient> SELECT * FROM tb OFFSET 5 ROWS;

The return result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    6 | a    |   80 |
+------+------+------+
1 row in set

Query the top 3 rows by id

obclient> SELECT * FROM tb ORDER BY id FETCH FIRST 3 ROWS ONLY;

The return result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set

Query the top 2 rows by id

obclient> SELECT id, name FROM tb ORDER BY id FETCH NEXT 2 ROWS ONLY;

The return result is as follows:

+------+------+
| ID   | NAME |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set

Query the top 30% of data by num

obclient> SELECT id, name,num FROM tb ORDER BY num
 FETCH FIRST 30 PERCENT ROWS ONLY;

The return result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    3 | a    |   50 |
+------+------+------+
1 row in set

Query the top 30% of data by num that have the lowest num values, and all other data the same as the last one obtained in the previous example

obclient>  SELECT id, name,num FROM tb ORDER BY num FETCH FIRST 30 PERCENT ROWS WITH TIES;

The return result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    3 | a    |   50 |
+------+------+------+
1 row in set

References

For more information about the SELECT syntax, see SELECT.
For more information about subqueries, see Subqueries.
For more information about query statement optimization, see Query rewrite overview.

OceanBase

Customer Stories

Documentation

Query a single table

Prerequisites

Syntax

Execution order of SELECT keywords

Notice

Create test tables and add test data

Basic queries

Query all columns

Specify a table alias during the query

Query specified columns

Set column aliases during the query

Eliminate rows with duplicate values during the query

Use ROWNUM to limit the number of returned rows during the query

Query calculated values

Query results of a function

Data filtering

Compare conditions

Equal to (=)

Not equal to (<> and !=)

Greater than (>) and less than (<)

Note

Logical conditions

AND

OR

Fuzzy queries (LIKE)

Note

Note

Range queries (BETWEEN AND)

Note

Specify multiple values (IN)

Notice

Queries that use NULL values (IS NULL/IS NOT NULL)

IS NULL

IS NOT NULL

Group queries

Group query examples

Single-field grouping query

Multi-field grouping query

Filter before grouping

Filter after grouping

Group by and sort

Group by and aggregate

Sorting queries

Sort by a single field

Display employee information sorted by deptno in ascending order

Display employee information sorted by deptno in descending order

Sort by multiple fields

Sort after WHERE

Sort using NLSSORT

Sort by Pinyin

Use Row_Limiting_Clause in queries

Query data after the Nth row

Example 1

Example 2

Query the top 3 rows by id

Query the top 2 rows by id

Query the top 30% of data by num

Query the top 30% of data by num that have the lowest num values, and all other data the same as the last one obtained in the previous example

References

Use `ROWNUM` to limit the number of returned rows during the query

Display employee information sorted by `deptno` in ascending order

Display employee information sorted by `deptno` in descending order