To query data that satisfies specified conditions, add a WHERE clause to the SELECT statement.
Syntax
To perform a condition query, add WHERE following the FROM clause to query records that comply with specified conditions and then use the SELECT statement to specify columns.
Statement syntax for condition queries:
SELECT select_list FROM table_list
WHERE query_condition
The WHERE keyword can be followed by one or multiple conditions. The conditions filter the preceding data that complies with the conditions specified by the WHERE clause.
Common query conditions
The following table lists common query conditions specified by the WHERE clause.
| Query type | Predicate |
|---|---|
| Comparison | =, >, <, >=, <=, !=, and <> |
| Logical (multiple conditions) | AND OR NOT |
| Fuzzy match (by characters) | LIKE NOT LIKE |
| Interval query (for specifying a range) | BETWEEN AND NOT BETWEEN AND |
| Specified set query | IN NOT IN |
| NULL value query | IS NULL IS NOT NULL |
Queries by using 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 (=)
Filters out specified columns and corresponding data that has equal values. If the value is a string, use single quotation marks (' ') or double quotation marks (" ") to enclose the value. Syntax:
SELECT column_name [,column_name...] FROM table_name WHERE column_name = const_value;
Sample code:
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 | 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;
Sample code:
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 values based on the ASCII codes and compared based on their positions. 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
Equal to or greater than (>=) and equal to or less than (<=)
Sample code:
obclient> SELECT empname, deptno FROM emp WHERE deptno>30;
+---------+--------+
| empname | deptno |
+---------+--------+
| JONES | 40 |
| FORD | 40 |
+---------+--------+
2 rows in set
obclient> SELECT empname, deptno FROM emp WHERE deptno>=30;
+---------+--------+
| empname | deptno |
+---------+--------+
| ALLEN | 30 |
| JONES | 40 |
| BLAKE | 30 |
| FORD | 40 |
+---------+--------+
4 rows in set
obclient> SELECT empname, deptno FROM emp WHERE deptno<30;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH | 20 |
| SCOTT | 20 |
+---------+--------+
2 rows in set
obclient> SELECT empname, deptno FROM emp WHERE deptno<=30;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH | 20 |
| ALLEN | 30 |
| BLAKE | 30 |
| SCOTT | 20 |
+---------+--------+
4 rows in set
Queries based on logical conditions
Logical operators include AND and OR and support queries based on multiple conditions.
AND
Data that meets the two conditions combined by AND is returned. Syntax:
SELECT column_name [,column_name...] FROM table_name WHERE
query_condition AND query_condition;
Sample code:
obclient> SELECT empname, deptno FROM emp WHERE deptno<=30 AND sal>1000;
+---------+--------+
| empname | deptno |
+---------+--------+
| ALLEN | 30 |
| BLAKE | 30 |
| SCOTT | 20 |
+---------+--------+
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;
Sample code:
obclient> SELECT empname, deptno FROM emp WHERE deptno<=30 OR sal>1000;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH | 20 |
| ALLEN | 30 |
| JONES | 40 |
| BLAKE | 30 |
| SCOTT | 20 |
| FORD | 40 |
+---------+--------+
6 rows in set
LIKE
The predicate LIKE matches strings. Syntax:
[NOT] LIKE pattern
The syntax queries tuples that match the pattern from the values of the specified attribute columns. The pattern can be a complete string or contain wildcards such as % and _. In the syntax:
The underscore (_) exactly matches one character in the value rather than one byte of a multi-byte character set.
The percent sign (%) matches zero or multiple characters in the value rather than bytes in a multi-byte character set. The pattern "%" cannot match
NULL.
The following sample code shows how to query employees whose names start with A and their department sequence numbers:
obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'A%';
+---------+--------+
| empname | deptno |
+---------+--------+
| ALLEN | 30 |
+---------+--------+
1 row in set
Notice
If the database character set uses ASCII, one Chinese character needs two underscores (
_); if the database character set uses GBK, one Chinese character needs one underscore (_).
BETWEEN AND
The BETWEEN AND operator selects values between two values. These values can be numerals, text, or dates. Syntax:
SELECT column_name [,column_name...] FROM table_name WHERE
[NOT] BETWEEN min_const_value AND max_const_value;
Notice
The critical 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.
Sample code:
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 allows you to specify multiple values in a WHERE clause as a set. The IN operator returns data in a specified column that matches a value in the set. The NOT IN operator returns data that does not match values 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 values in the
[NOT] INlist must be of the same type or compatible with each other.Wildcards are not supported for the
[NOT] INlist.
Sample code:
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
IS NULL/IS NOT NULL
The comparison operators and the LIKE, BETWEEN AND, IN, and NOT IN operators cannot yield accurate NULL values. Therefore, we recommend that you use the special query statements for NULL values:IS NULL and IS NOT NULL.
IS NULL
The IS NULL statement queries data whose value is NULL from the specified column. Syntax:
SELECT column_name [,column_name...] FROM table_name WHERE
column_name IS NULL;
Sample code:
obclient> CREATE TABLE tbl1 (col1 INT,col2 VARCHAR(10));
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 (col1,col2) VALUES (1,'a'),(NULL,'b'),(3,NULL),(NULL,'d'),
(5,'e');
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM tbl1 t WHERE t.col1 IS NULL;
+------+------+
| col1 | col2 |
+------+------+
| NULL | b |
| NULL | d |
+------+------+
2 rows in set
obclient> SELECT * FROM tbl1 t WHERE t.col1 IS NULL OR t.col2 IS NULL;
+------+------+
| col1 | col2 |
+------+------+
| NULL | b |
| 3 | NULL |
| NULL | d |
+------+------+
3 rows in set
IS NOT NULL
The IS NOT NULL statement queries data whose value is not NULL from the specified column. Syntax:
SELECT column_name [,column_name...] FROM table_name WHERE
column_name IS NOT NULL;
Sample code:
obclient> SELECT * FROM tbl1 t WHERE t.col1 IS NOT NULL;
+------+------+
| col1 | col2 |
+------+------+
| 1 | a |
| 3 | NULL |
| 5 | e |
+------+------+
3 rows in set
obclient> SELECT * FROM tbl1 t WHERE t.col1 IS NOT NULL AND t.col2 IS NOT NULL;
+------+------+
| col1 | col2 |
+------+------+
| 1 | a |
| 5 | e |
+------+------+
2 rows in set
The NULL-safe equal to operator (<=>) judges the NULL values and normal numerals. Sample code:
obclient> SELECT * FROM tbl1 t WHERE t.col1 <=> NULL;
+------+------+
| col1 | col2 |
+------+------+
| NULL | b |
| NULL | d |
+------+------+
2 rows in set