Simple query

A simple query refers to a query that retrieves data of one or multiple columns from one or multiple select lists or views in OceanBase Database. The number of columns, as well as their data type and length, are subject to the elements of the select list. A select list is the list of expressions between the SELECT keyword and the FROM clause.

If two or more tables contain columns with identical names, the column names must be qualified by table names. You can use a column alias (col_alias) to mark the expression that precedes the select list. You can use an alias in the ORDER BY clause, but not in other clauses of a query statement.

Syntax

Syntax of SELECT for a simple query:

SELECT column 1,column 2,column 3,… FROM table;

In the syntax, the table name, column names, and keywords SELECT and FROM are case-insensitive. A query statement can end with a semicolon (;). Multiple SQL statements can be executed concurrently. You can use a comment in the SELECT statement to transfer a directive or hint to the optimizer of OceanBase Database. The optimizer uses the hint to select an execution plan for the statement. For more information about hints, see Hint.

Examples

Create an employee table named employee, and insert data into the employee_id, first_name, last_name, manager_id, and salary columns.

CREATE TABLE employee (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
manager_id INT,
salary NUMERIC
);
INSERT INTO employee VALUES(111, 'DEL', 'FA BEN', 1, 1500);
INSERT INTO employee VALUES(112, 'AXEL', 'BELL', 1, 1000);
INSERT INTO employee VALUES(113, 'CRIS',  'RACHAR', 1, 1000);
INSERT INTO employee VALUES(114, 'BlAIR',  'WALDORF', 1, 800);

• Query part of columns in a table

  obclient> SELECT first_name, last_name, salary FROM employee;
  +------------+-----------+--------+
  | FIRST_NAME | LAST_NAME | SALARY |
  +------------+-----------+--------+
  | DEL        | FA BEN    |   1500 |
  | AXEL       | BELL      |   1000 |
  | CRIS       | RACHAR    |   1000 |
  | BlAIR      | WALDORF   |    800 |
  +------------+-----------+--------+
  4 rows in set
  

• Query all columns in a table

  obclient> SELECT * FROM employee;
  +-------------+------------+-----------+------------+--------+
  | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | MANAGER_ID | SALARY |
  +-------------+------------+-----------+------------+--------+
  |         111 | DEL        | FA BEN    |          1 |   1500 |
  |         112 | AXEL       | BELL      |          1 |   1000 |
  |         113 | CRIS       | RACHAR    |          1 |   1000 |
  |         114 | BlAIR      | WALDORF   |          1 |    800 |
  +-------------+------------+-----------+------------+--------+
  4 rows in set
  

• Perform arithmetical operations on columns in a table

  obclient> SELECT salary+100 FROM employee;
  +------------+
  | SALARY+100 |
  +------------+
  |       1600 |
  |       1100 |
  |       1100 |
  |        900 |
  +------------+
  4 rows in set
  

• Specify an alias for a column in a table

  obclient> SELECT salary*12 annual salary FROM employee;
  +--------+
  | ANNUAL SALARY   |
  +--------+
  |  18000 |
  |  12000 |
  |  12000 |
  |   9600 |
  +--------+
  4 rows in set
  

• Concatenate strings in a table

  obclient> SELECT first_name || '-' || last_name  AS name FROM employee;
  +---------------+
  | NAME          |
  +---------------+
  | DEL-FA BEN    |
  | AXEL-BELL     |
  | CRIS-RACHAR   |
  | BlAIR-WALDORF |
  +---------------+
  4 rows in set
  

• Deduplicate data in a table

  obclient> SELECT DISTINCT MANAGER_ID FROM employee;
  +------------+
  | MANAGER_ID |
  +------------+
  |          1 |
  +------------+
  1 row in set
  

• Use the CASE WHEN statement to query data in a table

  obclient> SELECT salary, CASE WHEN salary >= 1200 then 'High' WHEN salary >= 1000 THEN 'General'
       ELSE 'Keep trying' END AS type FROM employee;
  +--------+--------------+
  | SALARY | TYPE         |
  +--------+--------------+
  |   1500 | High         |
  |   1000 | General         |
  |   1000 | General         |
  |    800 | Keep trying     |
  +--------+--------------+
  4 rows in set