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.
SQL 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 WHERE 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:
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;
Examples:
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:
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 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 |
+---------+--------+---------+
| 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:
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.
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 | 30 |
+---------+--------+
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 | 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 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:
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] INset must be consistent or compatible with each other.- The values in the
[NOT] INset do not support wildcards.
Example:
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:
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;
Examples:
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. Examples:
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