Conditional queries|V4.1.0| docs|Distributed Database

Conditional queries

Last Updated：2023-07-28 02:55:42 Updated

To query data that satisfies specified conditions, add a WHERE clause to the SELECT statement.

Syntax

When a conditional query is executed, the records that meet the conditions specified by the WHERE clause are returned, and then the columns specified by the SELECT keyword are selected.

Statement syntax for conditional queries:

SELECT select_list FROM table_list
WHERE query_condition

The WHERE keyword can be followed by one or more conditions. The conditions filter the data specified by the FROM clause, and only data that meets the conditions is returned.

General query conditions

The following table lists general query conditions specified by the WHERE clause.

Condition type	Predicate
Comparison condition	=, >, <, >=, <=, !=, <>
Logical condition (multiple conditions supported in a query)	AND, OR, NOT
Fuzzy condition (matching by characters)	LIKE, NOT LIKE
Interval condition (with a specified range)	BETWEEN AND, NOT BETWEEN AND
Condition with a specified set	IN, NOT IN
Condition related to NULL values	IS NULL, IS NOT NULL

Queries with comparison operators

Comparison operators include equal to (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (!= and <>).

Equal to (=)

Returns data equal to the value in the specified column. If the value is a string, enclose the value with single quotation marks (' ') or double quotation marks (" "). Syntax:

SELECT column_name [,column_name...] FROM table_name WHERE column_name = const_value;

Examples:

Query the data of the empname and deptno columns whose deptno value is equal to 30 in the emp table.

obclient> SELECT empname, deptno FROM emp WHERE deptno = 30;
+---------+--------+
| empname | deptno |
+---------+--------+
| ALLEN   |     30 |
| BLAKE   |     30 |
+---------+--------+
2 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno = '30';
+---------+--------+
| empname | deptno |
+---------+--------+
| ALLEN   |     30 |
| BLAKE   |     30 |
+---------+--------+
2 rows in set

Query the data of the empname and deptno columns whose empname value is ALLEN in the emp table.

obclient> SELECT empname, deptno FROM emp WHERE empname='ALLEN';
+---------+--------+
| empname | deptno |
+---------+--------+
| ALLEN   |     30 |
+---------+--------+
1 row in set

Not equal to (<> and !=)

Not equal to operators include <> and !=. Syntax:

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;

Examples:

Query the data of the empname and deptno columns whose deptno value is not 30 in the emp table.

obclient> SELECT empname, deptno FROM emp WHERE deptno <> 30;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH   |     20 |
| JONES   |     40 |
| SCOTT   |     20 |
| FORD    |     40 |
+---------+--------+
4 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno != 30;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH   |     20 |
| JONES   |     40 |
| SCOTT   |     20 |
| FORD    |     40 |
+---------+--------+
4 rows in set

Greater than (>) and less than (<)

The greater than operator (>) and the less than operator (<) compare numbers. If characters are compared, they are converted into their respective ASCII codes, and then the ASCII codes are compared from left to right. Syntax:

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

The equal to or greater than (>=) and equal to or less than (<=) operators operate in a similar manner.

Examples:

Query the data of the empname and deptno columns whose deptno value is greater than 30 in the emp table.

obclient> SELECT empname, deptno FROM emp WHERE deptno > 30;
+---------+--------+
| empname | deptno |
+---------+--------+
| JONES   |     40 |
| FORD    |     40 |
+---------+--------+
2 rows in set

Query the data of the empname and deptno columns whose deptno value is greater than or equal to 30 in the emp table.

obclient> SELECT empname, deptno FROM emp WHERE deptno >= 30;
+---------+--------+
| empname | deptno |
+---------+--------+
| ALLEN   |     30 |
| JONES   |     40 |
| BLAKE   |     30 |
| FORD    |     40 |
+---------+--------+
4 rows in set

Query the data of the empname and deptno columns whose deptno value is smaller than 30 in the emp table.

obclient> SELECT empname, deptno FROM emp WHERE deptno < 30;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH   |     20 |
| SCOTT   |     20 |
+---------+--------+
2 rows in set

Query the data of the empname and deptno columns whose deptno value is smaller than or equal to 30 in the emp table.

obclient> SELECT empname, deptno FROM emp WHERE deptno <= 30;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     30 |
| BLAKE   |     30 |
| SCOTT   |     20 |
+---------+--------+
4 rows in set

Queries with logical conditions

Logical operators include AND and OR and support queries with multiple conditions.

AND

Data that meets both conditions combined by AND is returned. Syntax:

SELECT column_name [,column_name...] FROM table_name WHERE
query_condition AND query_condition;

Example:

Query the records whose deptno value is smaller than or equal to 30 and sal value is greater than 1000.

obclient> SELECT empname, deptno,sal FROM emp WHERE deptno <=30 AND sal>1000;
+---------+--------+---------+
| empname | deptno | sal     |
+---------+--------+---------+
| ALLEN   |     30 | 1600.00 |
| BLAKE   |     30 | 2850.00 |
| SCOTT   |     20 | 3000.00 |
+---------+--------+---------+
3 rows in set

OR

Data that meets either of the conditions combined by OR is returned. Syntax:

SELECT column_name [,column_name...] FROM table_name WHERE
query_condition OR query_condition;

Example:

Query the records whose deptno value is smaller than or equal to 30 or whose sal value is greater than 1000.

obclient> SELECT empname, deptno FROM emp WHERE deptno<=30 OR sal>1000;
+---------+--------+---------+
| empname | deptno | sal     |
+---------+--------+---------+
| SMITH   |     20 |  800.00 |
| ALLEN   |     30 | 1600.00 |
| JONES   |     40 | 2975.00 |
| BLAKE   |     30 | 2850.00 |
| SCOTT   |     20 | 3000.00 |
| FORD    |     40 | 3000.00 |
+---------+--------+---------+
6 rows in set

LIKE

The predicate LIKE matches strings. Syntax:

[NOT] LIKE pattern

The LIKE predicate searches the specified column value and returns the data that match the specified pattern. The pattern can be a complete string or contain wildcards % and _. Notes:

The underscore (_) exactly matches any character in the value.
The percent sign (%) matches zero or multiple characters in the value. The pattern "%" cannot match NULL.

Examples:

Query the data in the emp table whose empname value is in the format of "ALLE_", where the last bit is any character.

obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'ALLE_';
+---------+--------+
| empname | deptno |
+---------+--------+
| ALLEN   |     30 |
+---------+--------+
1 row in set

Query the data in the emp table whose empname value begins with letter "A".
```
obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'A%';
+---------+--------+
| empname | deptno |
+---------+--------+
| ALLEN   |     30 |
+---------+--------+
1 row in set
```
Notice

If the database character set is ASCII, one Chinese character needs two underscores (_); if the database character set uses GBK, one Chinese character needs only one underscore (_).

Notice

If the database character set is ASCII, one Chinese character needs two underscores (_); if the database character set uses GBK, one Chinese character needs only one underscore (_).

BETWEEN AND

The BETWEEN AND operator selects values between two values. These values can be numerals, literals, or dates. Syntax:

SELECT column_name [,column_name...] FROM table_name WHERE
[NOT] BETWEEN min_const_value AND max_const_value;

Notice

The bound values of an interval query cannot be exchanged because the values between them must be greater than or equal to the left value and less than or equal to the right value.

Example:

Query the records whose sal value ranges from 2000 to 2999.

obclient> SELECT * FROM emp WHERE sal BETWEEN 2000 AND 2999;
+-------+---------+---------+------+------------+---------+------+--------+
| empno | empname | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+---------+---------+------+------------+---------+------+--------+
|  1566 | JONES   | MANAGER | 1839 | 1981-04-02 | 2975.00 | NULL |     40 |
|  1698 | BLAKE   | MANAGER | 1839 | 1981-05-01 | 2850.00 | NULL |     30 |
+-------+---------+---------+------+------------+---------+------+--------+
2 rows in set

IN

The IN operator specifies multiple values in a WHERE clause. The values can be treated as a set. The IN operator returns data, in the specified column, that matches any value in the set. The NOT IN operator returns data, in the specified column, that does not match any value in the set. Syntax:

SELECT column_name [,column_name...] FROM table_name WHERE
column_name [NOT] IN (const_value,const_value,const_value...);

Notice

The value types in the [NOT] IN set must be consistent or compatible with each other.
The values in the [NOT] IN set do not support wildcards.

Example:

Query the records whose deptno value is in the set of (30,40,50,60).

obclient> SELECT * FROM emp WHERE deptno IN (30,40,50,60);
+-------+---------+----------+------+------------+---------+--------+--------+
| empno | empname | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+---------+----------+------+------------+---------+--------+--------+
|  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 |
|  1902 | FORD    | ANALYST  | 1566 | 1981-12-05 | 3000.00 |   NULL |     40 |
+-------+---------+----------+------+------------+---------+--------+--------+
4 rows in set

NULL value queries

The comparison operators and the LIKE, BETWEEN AND, IN, and NOT IN operators cannot accurately retrieve NULL values. Therefore, we recommend that you use the following special query conditions for NULL values: IS NULL and IS NOT NULL.

IS NULL

IS NULL queries NULL values from the specified column. Syntax:

SELECT column_name [,column_name...] FROM table_name WHERE
column_name IS NULL;

Example:

Query the records whose comm value is NULL.

obclient> SELECT * FROM emp WHERE comm IS NULL;
+-------+---------+---------+------+------------+---------+------+--------+
| empno | empname | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+---------+---------+------+------------+---------+------+--------+
|  1369 | SMITH   | CLERK   | 1902 | 1980-12-17 |  800.00 | NULL |     20 |
|  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 |
+-------+---------+---------+------+------------+---------+------+--------+
5 rows in set

IS NOT NULL

IS NOT NULL queries non-NULL values from the specified column. Syntax:

SELECT column_name [,column_name...] FROM table_name WHERE
column_name IS NOT NULL;

Example:

Query the records whose comm value is not NULL.

obclient> SELECT * FROM emp WHERE comm IS NOT NULL;
+-------+---------+----------+------+------------+---------+--------+--------+
| empno | empname | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+---------+----------+------+------------+---------+--------+--------+
|  1499 | ALLEN   | SALESMAN | 1698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
+-------+---------+----------+------+------------+---------+--------+--------+
1 row in set

You can also use the safe equal to operator (<=>) to judge the NULL values as well as normal numerals. Example:

obclient> SELECT empname, comm FROM emp WHERE comm <=> NULL;
+---------+------+
| empname | comm |
+---------+------+
| SMITH   | NULL |
| JONES   | NULL |
| BLAKE   | NULL |
| SCOTT   | NULL |
| FORD    | NULL |
+---------+------+
5 rows in set