Simple queries

A simple query retrieves one or more columns from one or more select lists or views in OceanBase Database. The number of columns and their data types and lengths are determined by the elements in the select list. A select list refers to the list of expressions that follows the SELECT keyword and precedes the FROM clause.

If two or more tables share common column names, you must qualify the column names with table names. You can use the column alias col_alias to mark the expression immediately preceding the select list. Aliases can be used in the ORDER BY clause but cannot be used in other clauses of the query.

Syntax

The SELECT statement for a simple query is as follows:

SELECT column1, column2, column3,... FROM table;

In this statement, the table name, column name, and keywords SELECT and FROM are case-insensitive. You can add a semicolon (;) at the end of the query. Multiple SQL statements can be executed simultaneously. You can use comments in the SELECT statement to pass instructions or hints to the OceanBase Database optimizer. The optimizer uses the hints to select an execution plan for the statement. For more information about hints, see Hint.

Examples

Create an employee table 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 some columns from the 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 from the 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 mathematical operations on the columns in the table

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

• Assign aliases to the columns in the table

  obclient> SELECT salary*12 AS annual_salary FROM employee;
  +--------+
  | annual_salary |
  +--------+
  |  18000 |
  |  12000 |
  |  12000 |
  |   9600 |
  +--------+
  4 rows in set
  

• Concatenate strings in the 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
  

• Remove duplicates from the table

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

• Use the CASE WHEN statement to query data from the table

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