The WHERE clause is essential when extracting data using SQL. It enables efficient data management by retrieving only the necessary data based on specified conditions. This article provides a detailed explanation covering the basics of the WHERE clause, its applications, and even methods for performance improvement.
This article uses examples based on the SCOTT sample schema.
You can import and use the SCOTT sample schema by executing the following:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger
1. What is the WHERE clause?
The WHERE clause is used in SQL SELECT statements to specify conditions. These conditions make it possible to efficiently extract only the required data.
The basic syntax is as follows:
SELECT column_name1, column_name2, ...
FROM table_name
WHERE condition;
Example:
The following query extracts employees whose salary (SAL) is greater than 2500.
SELECT empno, ename, sal
FROM emp
WHERE sal > 2500;
Result:
SQL> SELECT empno, ename, sal
2 FROM emp;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
14 rows selected.
SQL> SELECT empno, ename, sal
2 FROM emp
3 WHERE sal > 2500;
EMPNO ENAME SAL
---------- ---------- ----------
7566 JONES 2975
7698 BLAKE 2850
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000
2. Specifying Conditions Using Basic Operators
The basic operators available in the WHERE clause are summarized below.
| Operator | Description | Example |
| = | Equal to | sal = 3000 |
| != or <> | Not equal to | sal <> 3000 |
| > | Greater than | sal > 3000 |
| < | Less than | sal < 3000 |
| >= | Greater than or equal to | sal >= 3000 |
| <= | Less than or equal to | sal <= 3000 |
Practical Example:
Query to extract employees whose salary is between 2500 and 3000 (inclusive):
SELECT empno, ename, sal
FROM emp
WHERE sal BETWEEN 2500 AND 3000;
Result:
SQL> SELECT empno, ename, sal
2 FROM emp
3 WHERE sal BETWEEN 2500 AND 3000;
EMPNO ENAME SAL
---------- ---------- ----------
7566 JONES 2975
7698 BLAKE 2850
7788 SCOTT 3000
7902 FORD 3000
3. Combining Multiple Conditions
Use logical operators to specify multiple conditions.
| Logical Operator | Description | Example |
| AND | Meets all conditions | sal > 2000 AND deptno = 10 |
| OR | Meets any of the conditions | sal > 2000 OR deptno = 20 |
| NOT | Does not meet the condition | NOT (deptno = 10) |
Practical Example:
Get employees whose department number is 10 or 20 AND whose salary is 2500 or greater:
SELECT empno, ename, deptno, sal
FROM emp
WHERE (deptno = 10 OR deptno = 20) AND sal >= 2500;
Result:
SQL> SELECT empno, ename, deptno, sal
2 FROM emp
3 WHERE (deptno = 10 OR deptno = 20) AND sal >= 2500;
EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
7566 JONES 20 2975
7788 SCOTT 20 3000
7839 KING 10 5000
7902 FORD 20 3000
4. Specifying Special Conditions
Checking for NULL values
Check for NULL values using IS NULL or IS NOT NULL.
SELECT empno, ename, comm
FROM emp
WHERE comm IS NULL;
SELECT empno, ename, comm
FROM emp
WHERE comm IS NOT NULL;
Result:
SQL> SELECT empno, ename, comm
2 FROM emp;
EMPNO ENAME COMM
---------- ---------- ----------
7369 SMITH
7499 ALLEN 300
7521 WARD 500
7566 JONES
7654 MARTIN 1400
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER 0
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
SQL> SELECT empno, ename, comm
2 FROM emp
3 WHERE comm IS NULL;
EMPNO ENAME COMM
---------- ---------- ----------
7369 SMITH
7566 JONES
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
10 rows selected.
SQL> SELECT empno, ename, comm
2 FROM emp
3 WHERE comm IS NOT NULL;
EMPNO ENAME COMM
---------- ---------- ----------
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
7844 TURNER 0
Pattern Matching Using the LIKE Operator
The LIKE operator and wildcards enable partial match searches.
| Wildcard | Description |
| % | Any string (0 or more characters) |
| _ | Any single character |
Practical Example:
Get employees whose name starts with ‘S’:
SELECT empno, ename
FROM emp
WHERE ename LIKE 'S%';
Result:
SQL> SELECT empno, ename
2 FROM emp
3 WHERE ename LIKE 'S%';
EMPNO ENAME
---------- ----------
7369 SMITH
7788 SCOTT
Specifying Multiple Values with the IN Operator
Use the IN operator to concisely specify multiple values.
SELECT empno, ename, deptno
FROM emp
WHERE deptno IN (10, 20);
Result:
SQL> SELECT empno, ename, deptno
2 FROM emp;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
14 rows selected.
SQL> SELECT empno, ename, deptno
2 FROM emp
3 WHERE deptno IN (10, 20);
EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7566 JONES 20
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7876 ADAMS 20
7902 FORD 20
7934 MILLER 10
8 rows selected.
Specifying a Range with the BETWEEN Operator
Use BETWEEN to specify a range.
SELECT empno, ename, sal
FROM emp
WHERE sal BETWEEN 2000 AND 3000;
Result:
SQL> SELECT empno, ename, sal
2 FROM emp;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
14 rows selected.
SQL> SELECT empno, ename, sal
2 FROM emp
3 WHERE sal BETWEEN 2000 AND 3000;
EMPNO ENAME SAL
---------- ---------- ----------
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7902 FORD 3000
5. Practical Examples: Common Usage Scenarios
Specifying Date Data in Conditions
How to specify date conditions using Oracle’s DATE type:
SELECT empno, ename, hiredate
FROM emp
WHERE hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD')
AND TO_DATE('1982-12-31', 'YYYY-MM-DD');
Result:
SQL> SELECT empno, ename, hiredate
2 FROM emp
3 WHERE hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD')
4 AND TO_DATE('1982-12-31', 'YYYY-MM-DD');
EMPNO ENAME HIREDATE
---------- ---------- ---------
7499 ALLEN 20-FEB-81
7521 WARD 22-FEB-81
7566 JONES 02-APR-81
7654 MARTIN 28-SEP-81
7698 BLAKE 01-MAY-81
7782 CLARK 09-JUN-81
7839 KING 17-NOV-81
7844 TURNER 08-SEP-81
7900 JAMES 03-DEC-81
7902 FORD 03-DEC-81
7934 MILLER 23-JAN-82
11 rows selected.
Combination with Subqueries
Use subqueries to include data from other tables in the condition.
SELECT empno, ename, sal
FROM emp
WHERE deptno = (
SELECT deptno
FROM dept
WHERE dname = 'SALES'
);
Result:
SQL> SELECT empno, ename, sal
2 FROM emp
3 WHERE deptno = (
4 SELECT deptno
5 FROM dept
6 WHERE dname = 'SALES'
7 );
EMPNO ENAME SAL
---------- ---------- ----------
7499 ALLEN 1600
7521 WARD 1250
7654 MARTIN 1250
7698 BLAKE 2850
7844 TURNER 1500
7900 JAMES 950
6 rows selected.
6. Writing Performance-Conscious WHERE Clauses
Writing efficient WHERE clauses can improve query speed.
1. Utilize Indexes
Specifying indexed columns in conditions improves search speed.
2. Avoid Calculations
Applying calculations to columns within the condition may prevent indexes from being used.
Bad Example:
WHERE TO_CHAR(hiredate, 'YYYY') = '1982';
Good Example:
WHERE hiredate BETWEEN TO_DATE('1982-01-01', 'YYYY-MM-DD')
AND TO_DATE('1982-12-31', 'YYYY-MM-DD');
3. Omit Unnecessary Conditions
Adding unnecessary conditions complicates the query and degrades performance.
7. Summary
Understanding and mastering the WHERE clause enables efficient data extraction. We have provided a comprehensive explanation, from basic operators to complex condition specifications and performance improvements. Utilize these techniques to quickly retrieve the data you need!
[reference]
Oracle Database SQL Language Reference, 19c
コメント