Single-table queries|V4.1.0| docs|Distributed Database

Single-table queries

Last Updated：2023-08-01 06:02:28 Updated

A single-table SQL query is performed only in one table.

Syntax

Syntax for single-table queries:

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]
              [ LIMIT limit_clause ]

column_list:
 column_name[,column_name...]

If you use the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT keywords in a query, these clauses are executed following a strict sequence for executing these keywords:

The FROM clause is executed to find the required table.
The WHERE clause is executed to specify conditions.
The GROUP BY clause is executed to group or aggregate records. If GROUP BY is not executed, all records are considered a group.
The HAVING clause is executed to filter the grouped results.
The SELECT clause is executed.
The DISTINCT clause is executed to remove duplicate rows.
The ORDER BY clause is executed to sort the results in ascending or descending order.
Execute LIMIT to limit the number of records.

Notice

The difference between the WHERE and HAVING clauses is that the WHERE clause filters data before grouping or aggregation, whereas the HAVING clause filters data after grouping and returns the entire SQL query result.

SELECT queries

Create tables named emp and dept and insert data into the tables.

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

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

INSERT INTO dept VALUES (20,'ACCOUNTING','Los Angeles');
INSERT INTO dept VALUES (30,'OPERATIONS','CHICAGO');
INSERT INTO dept VALUES (40,'SALES','NEW YORK');

INSERT INTO emp VALUES (1698,'BLAKE','MANAGER',1839,'1981-5-1',2850,null,30);
INSERT INTO emp VALUES (1566,'JONES','MANAGER',1839, '1981-4-2',2975,null,40);
INSERT INTO emp VALUES (1788,'SCOTT','ANALYST',1566, '1987-7-15',3000,null,20);
INSERT INTO emp VALUES (1902,'FORD','ANALYST',1566, '1981-12-5',3000,null,40);
INSERT INTO emp VALUES (1369,'SMITH','CLERK',1902, '1980-12-17',800,null,20);
INSERT INTO emp VALUES (1499,'ALLEN','SALESMAN',1698, '1981-2-20',1600,300,30);

Query all columns

If you use an asterisk (*) when you query all data of the emp table, all fields in the table are returned. Example:

obclient> SELECT * FROM emp;
+-------+---------+----------+------+------------+---------+--------+--------+
| empno | empname | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+---------+----------+------+------------+---------+--------+--------+
|  1369 | SMITH   | CLERK    | 1902 | 1980-12-17 |  800.00 |   NULL |     20 |
|  1499 | ALLEN   | SALESMAN | 1698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  1566 | JONES   | MANAGER  | 1839 | 1981-04-02 | 2975.00 |   NULL |     40 |
|  1698 | BLAKE   | MANAGER  | 1839 | 1981-05-01 | 2850.00 |   NULL |     30 |
|  1788 | SCOTT   | ANALYST  | 1566 | 1987-07-15 | 3000.00 |   NULL |     20 |
|  1902 | FORD    | ANALYST  | 1566 | 1981-12-05 | 3000.00 |   NULL |     40 |
+-------+---------+----------+------+------------+---------+--------+--------+
6 rows in set

The preceding sample code is equivalent to:

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

Query specified columns

You can query data in specified columns based on column names.

Example:

Query the data in the empname and deptno columns of the emp table.

obclient> SELECT empname, deptno FROM emp;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     30 |
| JONES   |     40 |
| BLAKE   |     30 |
| SCOTT   |     20 |
| FORD    |     40 |
+---------+--------+
6 rows in set

You can set an alias for a column to be queried by using the AS + column alias clause or directly adding an alias behind the original column name.

obclient> SELECT empname AS Employee name, deptno AS Department No. FROM emp;
+--------------+---------------+
| Employee name| Department No.|
+--------------+---------------+
| SMITH        |           20  |
| ALLEN        |           30  |
| JONES        |           40  |
| BLAKE        |           30  |
| SCOTT        |           20  |
| FORD         |           40  |
+--------------+---------------+
6 rows in set

obclient> SELECT empname 'Employee name', deptno AS 'Department No.' FROM emp;
+--------------+---------------+
| Employee name| Department No.|
+--------------+---------------+
| SMITH        |           20  |
| ALLEN        |           30  |
| JONES        |           40  |
| BLAKE        |           30  |
| SCOTT        |           20  |
| FORD         |           40  |
+--------------+---------------+
6 rows in set

You can add the DISTINCT keyword in a query statement to deduplicate the query results.

obclient> SELECT deptno FROM emp;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
|     40 |
|     30 |
|     20 |
|     40 |
+--------+
6 rows in set

obclient> SELECT DISTINCT deptno FROM emp;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
|     40 |
+--------+
3 rows in set

You can use the LIMIT keyword to limit the number of rows returned per page. This feature is used to specify the number of pages to be returned.

obclient> SELECT empname, deptno FROM emp LIMIT 3;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     30 |
| JONES   |     40 |
+---------+--------+
3 rows in set

Query calculated values

You can process the data of specified columns in a query.

Examples:

Deduct the queried values of the sal column by 100 and then generate the result set.

obclient> SELECT empname, sal-100, deptno FROM emp;
+---------+---------+--------+
| empname | sal-100 | deptno |
+---------+---------+--------+
| SMITH   |  700.00 |     20 |
| ALLEN   | 1500.00 |     30 |
| JONES   | 2875.00 |     40 |
| BLAKE   | 2750.00 |     30 |
| SCOTT   | 2900.00 |     20 |
| FORD    | 2900.00 |     40 |
+---------+---------+--------+
6 rows in set

Display the values of the empname column in lowercase.

obclient> SELECT LOWER(empname), sal, deptno FROM emp;
+---------+---------+--------+
| empname | sal-100 | deptno |
+---------+---------+--------+
| smith   |  800.00 |     20 |
| allen   | 1600.00 |     30 |
| jones   | 2975.00 |     40 |
| blake   | 2850.00 |     30 |
| scott   | 3000.00 |     20 |
| ford    | 3000.00 |     40 |
+---------+---------+--------+
6 rows in set

In addition, you can use the WHERE condition to query specified columns. For more information, see Conditional queries.

For more information, see Use operators and functions in queries.