Single-table queries|V4.4.2|OceanBase Database| docs|Distributed Database

This topic describes how to perform single-table queries in OceanBase Database using SQL statements.

Prerequisites

You have connected to an Oracle-compatible 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 current user's privileges, see View user privileges. If you do not have this privilege, contact the administrator to grant it to you. For more information about user authorization, see Grant privileges directly.

Syntax

Use the SELECT statement to query data.

The general structure of a SELECT statement for querying a single table 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...]

Parameter description:

Parameter	Description
select_list	List of columns to retrieve, which can be column names, expressions, aggregate functions, etc. Multiple columns can be separated with commas.
table_name	The name of the table from which data is to be retrieved.
WHERE query_condition	Optional. Specifies the search criteria. Only rows that meet the criteria are returned.
GROUP BY group_by_condition	Optional. Specifies the column by which to group the results. This clause is often used together with aggregate functions.
HAVING group_condition	Optional. Filters the result set after grouping and returns only the groups that meet the specified conditions.
ORDER BY column_list	Optional. Specifies the columns to sort the result set by. You can specify one or more columns.
ASC	DESC	Optional. Specifies the sorting order. ASC indicates ascending order (default), and DESC indicates descending order.
LIMIT limit_clause	Optional. Specifies the number of rows to return or the percentage of rows to return, which limits the number of rows returned by the query and implements pagination. You can also specify an offset. You can use the ORDER BY clause to ensure a consistent sorting order for consistent results.
column_list	The parameter for specifying the columns to retrieve. You can specify a single column or multiple columns, separated by commas.
column_name	The name of the column to be retrieved.

Overview of the execution order of SELECT keywords

When keywords such as WHERE, GROUP BY, HAVING, and ORDER BY are used together, their execution order is strictly defined. The execution order is as follows:

Execute FROM to find the table.
Execute WHERE to specify the constraints.
Execute GROUP BY to group each record fetched. If there is no GROUP BY, the entire result set is treated as one group.
Execute HAVING to filter the results of the groups.
Execute SELECT.
Execute DISTINCT to remove duplicates.
Execute ORDER BY to sort the results in ascending or descending order based on the specified conditions.
Execute ROWNUM to limit the number of rows displayed.

Notice

The difference between WHERE and HAVING is that WHERE filters data before grouping, while HAVING filters the results after grouping to finally return the query result of the entire SQL statement.

Create a test table 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 rows of data 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 rows of data 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 in the table. Example:

obclient [SYS]> SELECT * FROM emp;

The result is as follows:

+-------+---------+----------+------+-----------+------+------+--------+------+
| 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;

The result is as follows:

+---------+-------+----------+------+-----------+------+------+--------+
| 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 when querying

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

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

The result is as follows:

+-------+---------+----------+------+-----------+------+------+--------+------+
| 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 specific columns

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

The query result is as follows:

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

Set aliases for columns during the query

obclient [SYS]> SELECT empname AS Employee Name, deptno AS Department Number FROM emp;

The query result is as follows:

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

Eliminate duplicate rows with duplicate values during the query

obclient [SYS]> SELECT DISTINCT age FROM emp;

The query result is as follows:

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

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

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

The query result is as follows:

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

Query computed values

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

The query result is as follows:

+---------+---------+----------+
| 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 apply functions to specified columns in a query. For example, the following query converts the job column to lowercase. For more information, see Use operators and functions in queries.

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

The query result is as follows:

+---------+---------+------------+
| 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 using a FROM function

In OceanBase Database's Oracle-compatible mode, you can use the syntax SELECT * FROM FUNCTION(*); to call a function that returns a table type and query its result as table data.

When using SELECT * FROM FUNCTION(*);, note the following:

The data type returned by the function must be a table type.
The number and types of columns returned by the table function must correspond one-to-one with the number and types of columns required by the caller.
The number of rows returned by the table function must be greater than or equal to the number of rows required by the caller.
The number and types of function parameters must match the function definition.

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

For more information about creating functions, see CREATE FUNCTION and Create a function.

Example:

Define a table type emp_type.

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

Define a set type emp_type_list that contains table types.

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

Create a function get_emp_info 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 function's return result.

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

The query result is as follows:

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

Data filtering

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

When using the WHERE clause, ensure the conditions are correct and appropriate operators are used.

The following table lists common query conditions used in the WHERE clause.

Query condition type	Predicate
Comparison query	=,>,<,>=,<=,!=,<>
Logical query (multiple conditions)	AND,OR,NOT
Fuzzy Query (Character Matching)	LIKE,NOT LIKE
Range query (exact range)	BETWEEN AND,NOT BETWEEN AND
Specified set query	IN,NOT IN
Querying for NULL values	IS NULL,IS NOT NULL

Comparison condition queries

Equal to (=)

Queries data in the specified column that is equal to the target value. If the value is of string type, it must be enclosed 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" includes two notations: <> and !=. The general format is:

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;

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 (\<)

Greater than (>) and less than (<) compare numerical values. When comparing characters, they are compared based on their ASCII code values, with comparisons done character by character in order. The general format is:

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

Greater than or equal to (\>=) and less than or equal to (\<=) work similarly.

Example:

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

The 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 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 result is as follows:

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

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

The result is as follows:

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

Logical condition queries

The logical operators AND (AND) and OR (OR) support queries with multiple conditions.

AND

Returns data that satisfies both AND conditions. The general format is:

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

Example:

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

The result is as follows:

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

OR

Data that meets either of the conditions will be returned. The general format is:

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

An example is as follows:

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 search (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 where the value in the specified attribute column matches the pattern. pattern can be a complete string or contain the wildcard characters % and _. Where:

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

Note

In OceanBase Database's Oracle-compatible mode, if the following conditions are met, the like operator is replaced with the = operator for comparison:

pattern does not contain the % or _ wildcard, and there is no escape.
The field type is not LOB. In Oracle-compatible mode, the LOB type does not support = comparison, so it cannot be converted.
pattern is not a fixed-length char or nchar type.

The following example queries for employees whose first four letters of their 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

The following example queries for employees whose first letter of their 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

Notice

When the database character set is ASCII, each Chinese character requires two _ characters. When the character set is GBK, only one _ character is needed.

Range query (BETWEEN AND)

The BETWEEN ... AND operator retrieves data that falls within a range of two values. These values can be numeric, text, or date. The general format is:

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

Notice

The two boundary values in a range query must not be swapped. The value must be greater than or equal to the left value and less than or equal to the right 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

Specified set query (IN)

The IN operator allows you to specify multiple values in the WHERE clause as a set. IN means that any data in the specified column that matches any value in the set will be returned. NOT IN means that data that does not match any value in the set will be returned. The general format is:

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

Notice

The value types in the [NOT] IN set must be consistent or compatible with each other.
Wildcards are not supported in the values of the [NOT] IN set.

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 for NULL values (IS NULL/IS NOT NULL)

Because comparison operators, LIKE, BETWEEN AND, IN, and NOT IN yield inaccurate results when querying NULL values, it is recommended to use the dedicated NULL value query statements IS NULL and IS NOT NULL.

IS NULL

IS NULL is used to query data where the value of the specified column is NULL. The general format is:

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

An example is as follows:

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

IS NOT NULL is used to query data where the value of the specified column is NOT NULL. The general format is:

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

An example is as follows:

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 query

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

GROUP BY supports grouping by a single field or multiple fields. 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 lists the commonly used aggregate functions in grouping.

Aggregate functions	Purpose
MAX()	Query the maximum value of a specified column.
MIN()	Query the minimum value of a specified column.
COUNT()	Counts the number of rows in the query result.
SUM()	Returns the total of specified columns.
AVG()	Returns the average value of the data in the specified column.

Sample grouped query

Create the sample table fruit_order.

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 appropriate 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 number of orders placed by each customer and output the customer ID and the number of orders.

obclient [SYS]> SELECT user_id Customer ID, COUNT(order_id) Number of Orders FROM fruit_order GROUP BY user_id;

The return result is as follows:

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

Query with multiple field grouping

Query the number of orders placed by each customer per year, and output the customer ID, order year, and number of orders.

obclient [SYS]> SELECT user_id Customer ID, order_year Order Year, COUNT(order_id) Number of Orders FROM fruit_order GROUP BY user_id,order_year;

The return result is as follows:

+----------+--------------+--------------+
| Customer ID | Order Year | Number of Orders |
+----------+--------------+--------------+
|     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 number of orders placed by each customer in 2020, and output the customer ID and number of orders.

obclient [SYS]> SELECT user_id Customer ID, COUNT(order_id) Number of Orders FROM fruit_order t WHERE t.order_year = '01-JAN-20' GROUP BY user_id;

The return result is as follows:

+----------+--------------+
| Customer ID | Number of Orders |
+----------+--------------+
|     1011 |            1 |
+----------+--------------+
1 row in set

Filter after grouping

Query customers who placed at least one order in 2019, and output the customer ID and number of orders.

obclient [SYS]> SELECT user_id Customer ID, COUNT(order_id) Number of Orders 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

Sort after grouping

Query the maximum amount for each customer's orders, and then output the customer IDs and maximum amounts in descending order.

obclient [SYS]> SELECT user_id Customer ID, MAX(fruit_price) Maximum Amount FROM fruit_order t GROUP BY user_id ORDER BY Maximum Amount DESC;

The result is as follows:

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

Group and summarize

Use the GROUP BY CUBE clause to group the fruit_order table by user_id and summarize the grouped results.

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

The 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 summarizing, see SIMPLE SELECT.

Sort queries

The ORDER BY clause is used to arrange the query results in ascending (ASC) or descending (DESC) order based on one or more attribute columns. The default is ascending order.

Query customers who placed orders in 2019 with a quantity greater than or equal to 1, and output the 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 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 | Li Si | 84.66 |
| 1033 | Wang Wu | 115.21 |
+---------+-----------+------------------+
10 rows in set

Single-field sorting queries

Display employee information in ascending order by `deptno`

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 in descending order by `deptno`

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

Multi-field sorting queries

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

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 the WHERE clause

ORDER BY can be used to sort 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 the NLSSORT function

You can use the NLSSORT function for pinyin sorting or stroke-order sorting of Chinese text.

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 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 a SELECT query and is commonly used for pagination.

Note

For more information and best practices on paginated queries, see Paginated queries.

Query data after the N-th row

Example 1

Query data after the 4th row.

obclient> SELECT * FROM tb OFFSET 4 ROW;

The 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 result is as follows:

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

Query the 3 data records with the lowest id

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

The result is as follows:

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

Query the 2 data records with the lowest id

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

The result is as follows:

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

Query the top 30% of data in num

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

The query results are as follows:

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

Query the data within the lowest 30% of the `num` column, along with all other data that is equal to the last data obtained in the previous example.

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

The query results are as follows:

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

References

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

OceanBase

Customer Stories

Documentation

Single-table queries

Prerequisites

Syntax

Overview of the execution order of SELECT keywords

Notice

Create a test table and add test data

Basic queries

Query all columns

Specify a table alias when querying

Query specific columns

Set aliases for columns during the query

Eliminate duplicate rows with duplicate values during the query

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

Query computed values

Query using a FROM function

Data filtering

Comparison condition queries

Equal to (=)

Not equal to (\<> and !=)

Greater than (>) and less than (\<)

Note

Logical condition queries

AND

OR

Fuzzy search (LIKE)

Note

Notice

Range query (BETWEEN AND)

Notice

Specified set query (IN)

Notice

Queries for NULL values (IS NULL/IS NOT NULL)

IS NULL

IS NOT NULL

Group query

Sample grouped query

Single-field grouping query

Query with multiple field grouping

Filter before grouping

Filter after grouping

Sort after grouping

Group and summarize

Sort queries

Single-field sorting queries

Display employee information in ascending order by deptno

Display employee information in descending order by deptno

Multi-field sorting queries

Sort after the WHERE clause

Sort using the NLSSORT function

Sort by Pinyin

Use Row_Limiting_Clause in queries

Note

Query data after the N-th row

Example 1

Example 2

Query the 3 data records with the lowest id

Query the 2 data records with the lowest id

Query the top 30% of data in num

Query the data within the lowest 30% of the num column, along with all other data that is equal to the last data obtained in the previous example.

References

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

Display employee information in ascending order by `deptno`

Display employee information in descending order by `deptno`

Query the data within the lowest 30% of the `num` column, along with all other data that is equal to the last data obtained in the previous example.