Conditional queries|V4.1.0| docs|Distributed Database

Conditional queries

Last Updated：2023-08-01 06:02:28 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 following the FROM 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;

Example:

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

obclient> SELECT empname, deptno FROM emp WHERE empname='ALLEN';
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
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;

Example:

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

obclient> SELECT empname, deptno FROM emp WHERE deptno != 30;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
5 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.

Example:

obclient> SELECT empname, deptno FROM emp WHERE deptno > 30;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
| JONES   |     40 |
| FORD    |     45 |
+---------+--------+
3 rows in set

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

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

obclient> SELECT empname, deptno FROM emp WHERE deptno <= 30;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| BLAKE   |     30 |
| SCOTT   |     25 |
+---------+--------+
3 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:

obclient> SELECT empname, deptno, sal FROM emp WHERE deptno<=30 AND sal > 1000;
+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| BLAKE   |     30 | 2850 |
| SCOTT   |     25 | 3000 |
+---------+--------+------+
2 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:

obclient> SELECT empname, deptno, sal FROM emp WHERE deptno <= 30 OR sal > 1000;
+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| SMITH   |     20 |  800 |
| ALLEN   |     35 | 1600 |
| JONES   |     40 | 2975 |
| BLAKE   |     30 | 2850 |
| SCOTT   |     25 | 3000 |
| FORD    |     45 | 3000 |
+---------+--------+------+
6 rows in set

LIKE

The predicate LIKE matches strings. Syntax:

[NOT] LIKE pattern

The LIKE predicate searches the specified attribute columns and returns the tuples that match the specified pattern. The pattern can be a complete string or contain wildcards such as % and _. Note that:

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.

Note

In the Oracle mode of the OceanBase Database, the like operator is replaced with = when the following conditions are met:

pattern does not contain the wildcard characters % or _, and does not have an escape character.
The column type is not LOB. In Oracle mode, the LOB type does not support = comparison, so it cannot be converted.
pattern is not of the fixed-length char or nchar type.

In the following example, the first four letters of the employee name are ALLE and the last letter is any character.

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

In the following example, the first letter of the employee name is A.

obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'A%';
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

Notice

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

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:

obclient> SELECT * FROM emp WHERE sal BETWEEN 2000 AND 2999;
+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
+-------+---------+---------+------+-----------+------+------+--------+------+
2 rows in set

IN

The IN operator allows you to specify 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:

obclient> SELECT * FROM emp WHERE deptno IN (30,40,50,60);
+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
+-------+---------+---------+------+-----------+------+------+--------+------+
2 rows in set

IS NULL/IS NOT NULL

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:

obclient> SELECT * FROM emp WHERE comm IS NULL;
+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK   | 1902 | 17-DEC-80 |  800 | NULL |     20 |   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 |
+-------+---------+---------+------+-----------+------+------+--------+------+
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:

obclient> SELECT * FROM emp WHERE comm IS NOT NULL;
+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
1 row in set

Releated topic

For information about pattern-matching conditions, see Pattern-matching conditions.