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 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.
The ROWNUM clause is executed to limit the number of records displayed per page.

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 proper data to 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),
    age           NUMBER(2,0),
    CONSTRAINT PK_emp PRIMARY KEY (empno),
    CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)  
);


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

INSERT INTO dept VALUES
(20,'Finance','beijing'),
(35,'Administration','hangzhou'),
(40,'Development','xian'),    
(30,'Workshop','guangzhou'),    
(25,'Legal affairs','shanghai'),    
(45,'Office','suzhou');

Query all columns

An asterisk (*) indicates to return all fields in a table. Example:

obclient> SELECT * FROM emp;
+-------+---------+----------+------+-----------+------+------+--------+------+
| 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

The preceding sample code is equivalent to:

obclient> SELECT empname,empno,job,mgr,hiredate,sal,comm,deptno FROM emp;
+---------+-------+----------+------+-----------+------+------+--------+
| 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

Query specified columns

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

You can set aliases for columns in a query. Example:

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

You can remove duplicate rows in a query. Example:

obclient> SELECT age FROM emp;
+------+
| AGE  |
+------+
|   22 |
|   22 |
|   22 |
|   33 |
|   33 |
|   22 |
+------+
6 rows in set

obclient> SELECT DISTINCT age FROM emp;
+------+
| AGE  |
+------+
|   22 |
|   33 |
+------+
2 rows in set

You can use the ROWNUM clause to limit the number of rows returned per page. This feature is often used in pagination. Example:

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

Query calculated values

obclient> SELECT empname, sal-100,job 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

You can apply functions to specified columns in the query. For example, in the following sample code, the LOWER function is used to convert job to lowercase. For more information, see "Use operators and functions in queries".

obclient> SELECT empname, sal-100, LOWER(job) FROM emp;
+---------+---------+---------------+
| empname | sal-100 | LOWER(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

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.