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

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

Prerequisites

You have connected to an Oracle tenant of OceanBase Database. For more information, 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 the privilege, contact the administrator to grant the privilege. For more information, see Grant privileges directly.

Syntax

Use the SELECT statement to query data.

The general syntax of the 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	The list of columns to retrieve, which can be column names, expressions, or aggregate functions. You can separate multiple columns with commas.
table_name	The name of the table from which to retrieve data.
WHERE query_condition	An optional parameter that specifies the retrieval condition. Only rows that meet the condition will be returned.
GROUP BY group_by_condition	An optional parameter that specifies the columns to group the results by. It is typically used with aggregate functions.
HAVING group_condition	An optional parameter that filters the grouped result set, returning only the groups that meet the condition.
ORDER BY column_list	An optional parameter that specifies the columns to sort the result set by. You can specify one or more columns for sorting.
ASC	DESC	An optional parameter that specifies the sort order. ASC indicates ascending order (default), and DESC indicates descending order.
LIMIT limit_clause	An optional parameter that limits the number of rows returned by the query to implement pagination. You can specify an offset, the number of rows to return, or the percentage of rows to return. You can combine it with the ORDER BY clause to ensure the sorting order and obtain consistent results.
column_list	A parameter that specifies the columns to retrieve. It can be a single column or multiple columns separated by commas.
column_name	The name of the column to retrieve.

Execution order of the SELECT keyword

When the WHERE, GROUP BY, HAVING, and ORDER BY keywords are used together, there are clear restrictions on the order of execution. The execution order of the keywords is as follows:

Execute FROM to find the table.
Execute WHERE to specify the constraint conditions.
Execute GROUP BY to group each record retrieved. If GROUP BY is not specified, all records are grouped as a single group.
Execute HAVING to filter the grouped results.
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 result rows displayed.

Notice

The difference between WHERE and HAVING is that WHERE filters data before grouping, while HAVING filters data after grouping. Finally, the entire SQL query result is returned.

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 6 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 6 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 that all fields are returned in the table. Here's an 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 returned 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

Use table aliases when you query

Query the emp table and specify the 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 a specified column

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

The results are as follows:

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

Set aliases for columns while querying

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

The results returned are as follows:

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

Eliminate rows with repeated values during queries

obclient [SYS]> SELECT DISTINCT age FROM emp;

The returned result is as follows:

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

You can use the `ROWNUM` clause to limit the number of returned rows during a query

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

The output 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 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

In the following example, a function is used in the WHERE clause to convert the value in 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 following results return:

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

FROM Query

In OceanBase Database in Oracle mode, you can call a function that returns a table by using the SELECT * FROM FUNCTION(*); statement. Then, you can query the result returned by the function as a table.

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

The return type of the function must be table type.
The number and type of columns returned by the table function must correspond one-to-one with the columns required by the calling function.
The number of rows returned by the table function must be greater than or equal to the required number of rows.
The number and types of arguments passed must match the function definition.

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

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

Here's 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 a collection type emp_type_list containing the table type.

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 ;

Check the function result.

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

The returned result is as follows:

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

Data filtering

When querying data that meets specific conditions, you can filter the data by adding a WHERE clause to the SELECT query statement. The WHERE clause can contain one or more conditions that are used to filter the data. Only data that meets the WHERE conditions will be returned. You can flexibly apply query conditions to filter and retrieve the target data as needed.

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

The following table lists the common query conditions 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 (determining a range)	BETWEEN AND, NOT BETWEEN AND
Set query	IN, NOT IN
NULL value query	IS NULL, IS NOT NULL

Comparison conditions

Equal (=)

Queries data where the specified column matches the target value. If the value is a string, it should be enclosed in single or double quotes.

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

The result is as follows:

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

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

The result is as follows:

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

Not equal (<> and !=)

Not equal can be written as <> or !=. 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;

Here is an example:

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

The 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 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. If comparing characters, they are compared based on their ASCII values, character by character. 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.

Here is an 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 conditions

Logical operators AND and OR support queries with multiple conditions.

AND

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

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

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

OR

Returns data that satisfies at least one of the conditions specified by OR. The general format is:

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

Pattern matching (LIKE)

The LIKE predicate is used for string matching. The general format is:

[NOT] LIKE pattern

The syntax means to find tuples where the specified attribute column value matches the pattern. The pattern can be a complete string or contain wildcards % and _. Specifically:

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

Note

In OceanBase Database's Oracle mode, the like operator is converted to = under the following conditions:

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

The following example queries data where the employee name starts with the four letters ALLE and ends with any character.

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

The result is as follows:

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

The following example queries data where the employee name starts with the letter A.

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

The result is as follows:

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

Notice

When the database character set is ASCII, one Chinese character requires two _ wildcards; when the character set is GBK, only one _ wildcard is needed.

Range query (BETWEEN AND)

The BETWEEN ... AND operator selects data within the range specified by the two values. These values can be numerical, text, or dates. 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 cannot be swapped. The values must be greater than or equal to the left value and less than or equal to the right value.

Here is an example:

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

The 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

IN clause

The IN operator allows you to specify multiple values in the WHERE clause as a set. IN returns data that matches any value in the set. NOT IN returns data that does not match any value in the set. The general syntax is:

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 or compatible type.
The [NOT] IN set does not support wildcards.

Here is an example:

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

The result set 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

NULL value queries (IS NULL/IS NOT NULL)

The comparison operators, LIKE, BETWEEN AND, IN, and NOT IN do not accurately query NULL values. We recommend that you use the IS NULL and IS NOT NULL statements to query NULL values.

IS NULL

IS NULL queries data whose value in the specified column is NULL. The general syntax is:

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 result set 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 queries data whose value in the specified column is not NULL. The general syntax is:

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 result set 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 results in ascending (ASC) or descending (DESC) order based on one or more attribute columns. The default sorting 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 function	Description
MAX()	Query the maximum value of the specified column.
MIN()	Query the minimum value of the specified column.
COUNT()	Count the number of rows in the query result.
SUM()	Return the sum of the specified column.
AVG()	Return the average value of the specified column.

Group by query examples

Create a sample table named 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 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 group by 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 CustomerID, COUNT(order_id) OrderCount FROM fruit_order GROUP BY user_id;

The return result is as follows:

+----------+--------------+
| CustomerID | OrderCount |
+----------+--------------+
|     1011 |            3 |
|     1022 |            4 |
|     1033 |            2 |
+----------+--------------+
3 rows in set

Multiple field group by query

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

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

The return result is as follows:

+----------+--------------+--------------+
| CustomerID | OrderYear | OrderCount |
+----------+--------------+--------------+
|     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

Filtering before grouping

Query the number of orders placed by each customer in 2020 and output the customer ID and the number of orders.

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

The return result is as follows:

+----------+--------------+
| CustomerID | OrderCount |
+----------+--------------+
|     1011 |            1 |
+----------+--------------+
1 row in set

Filtering after grouping

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

obclient [SYS]> SELECT user_id CustomerID, COUNT(order_id) 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:

+----------+--------------+
| CustomerID | OrderCount |
+----------+--------------+
|     1011 |            1 |
+----------+--------------+
1 row in set

Sorting after grouping

Query the maximum order amount for each customer and output the customer ID and the maximum order amount in descending order.

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

The return result is as follows:

+----------+--------------+
| CustomerID | MaxAmount |
+----------+--------------+
|     1033 |        63.66 |
|     1011 |        58.83 |
|     1022 |        34.44 |
+----------+--------------+
3 rows in set

Grouping and summarizing

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

Order by query

The ORDER BY clause specifies the attribute column(s) in the query result set to be sorted in ascending (ASC) or descending (DESC) order, defaulting to ascending.

Query for customers whose order quantity in 2019 is greater than or equal to 1, and output customer ID and order quantity.

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 | Lisi      |            84.66 |
|    1033 | WANG Wu    |           115.21 |
+---------+-----------+------------------+
10 rows in set

Single-field sort query

Show employee information in ascending order of `deptno`

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

The results are returned 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 query result is as follows:

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

Queries with multiple field sorting

Display information about employees in ascending order of deptno, and descending order of sal.

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

The results are 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

You can use the ORDER BY clause with the WHERE clause. Here is an 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

Use the NLSSORT function to sort data

Use the NLSSORT function to sort in ways such as pinyin sorting and stroke-sorting for Chinese characters.

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 returned 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 the Row_Limiting_Clause in a query

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

Note

For more information about pagination queries, see Pagination queries.

Query data after the Nth row

Example 1

Query data after the 4th row.

obclient> SELECT * FROM tb OFFSET 4 ROW;

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

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

Query the 3 lowest id values

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

The query result is as follows:

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

Query the 2 lowest id values

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

The query result is as follows:

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

Query the first 30% of data in the num column

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

The query result is as follows:

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

Query the first 30% of the lowest num values

Query the first 30% of the lowest num values, and all data with the same value as the last row in the previous example

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

The query 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 paginated queries, see Paginated queries.
For more information about subqueries, see Subqueries.
For more information about query statement optimization, see Overview of query rewriting.

Customer Stories

Documentation

Single-table query

Prerequisites

Syntax

Execution order of the SELECT keyword

Notice

Create a test table and add test data

Basic queries

Query all columns

Use table aliases when you query

Query a specified column

Set aliases for columns while querying

Eliminate rows with repeated values during queries

You can use the ROWNUM clause to limit the number of returned rows during a query

Query computed values

FROM Query

Data filtering

Comparison conditions

Equal (=)

Not equal (<> and !=)

Greater than (>) and less than (<)

Note

Logical conditions

AND

OR

Pattern matching (LIKE)

Note

Notice

Range query (BETWEEN AND)

Notice

IN clause

Notice

NULL value queries (IS NULL/IS NOT NULL)

IS NULL

IS NOT NULL

Group queries

Group by query examples

Single field group by query

Multiple field group by query

Filtering before grouping

Filtering after grouping

Sorting after grouping

Grouping and summarizing

Order by query

Single-field sort query

Show employee information in ascending order of deptno

Display employee information in descending order by deptno

Queries with multiple field sorting

Sort after the WHERE clause

Use the NLSSORT function to sort data

Sort by Pinyin

Use the Row_Limiting_Clause in a query

Note

Query data after the Nth row

Example 1

Example 2

Query the 3 lowest id values

Query the 2 lowest id values

Query the first 30% of data in the num column

Query the first 30% of the lowest num values

References

You can use the `ROWNUM` clause to limit the number of returned rows during a query

Show employee information in ascending order of `deptno`

Display employee information in descending order by `deptno`