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.
Common query conditions
The following table lists common query conditions specified by the WHERE clause.
| Condition type | Predicate |
|---|---|
| Comparison condition | =, >, <, >=, <=, !=, and <> |
| 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 specified column and value. 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;
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 | 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;
Sample code:
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 the similar way.
Sample code:
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;
Sample code:
obclient> SELECT empname, deptno FROM emp WHERE deptno<=30 AND sal>1000;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| BLAKE | 30 |
| SCOTT | 25 |
+---------+--------+
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;
Sample code:
obclient> SELECT empname, deptno FROM emp WHERE deptno<=30 OR sal>1000;
+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH | 20 |
| ALLEN | 35 |
| JONES | 40 |
| BLAKE | 30 |
| SCOTT | 25 |
| FORD | 45 |
+---------+--------+
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 _. The wildcards serve the following purposes:
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.
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 | 35 |
+---------+--------+
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 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.
Sample code:
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 values in the
[NOT] INlist must be of the same type or compatible with each other.Wildcards are not allowed in the
[NOT] INlist.
Sample code:
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;
Sample code:
obclient> CREATE TABLE tbl11 (col1 INT,col2 VARCHAR(10));
Query OK, 0 rows affected
obclient> INSERT INTO tbl11 (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 tbl11 t WHERE t.col1 IS NULL;
+------+------+
| COL1 | COL2 |
+------+------+
| NULL | b |
| NULL | d |
+------+------+
2 rows in set
obclient> SELECT * FROM tbl11 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
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;
Sample code:
obclient> SELECT * FROM tbl11 t WHERE t.col1 IS NOT NULL;
+------+------+
| COL1 | COL2 |
+------+------+
| 1 | a |
| 3 | NULL |
| 5 | e |
+------+------+
3 rows in set
obclient> SELECT * FROM tbl11 t WHERE t.col1 IS NOT NULL AND t.col2 IS NOT NULL;
+------+------+
| COL1 | COL2 |
+------+------+
| 1 | a |
| 5 | e |
+------+------+
2 rows in set