The WHERE clause is essential when retrieving data from Oracle SQL tables.
This article provides a thorough explanation—from basic usage to more advanced filtering conditions—using easy-to-understand diagrams and real SQL examples.
For more information on select, click here.
- 1. What is the WHERE Clause?
- 2. Basic Syntax of the WHERE Clause
- 3. Sample Table Used (SCOTT Schema)
- 4. Simple Filtering Condition
- 5. Filtering by String and Case Sensitivity
- 6. 🔍 Why Using UPPER Ignores Case
- 7. Using Comparison Operators
- 8. Using Multiple Conditions (AND / OR)
- 9. Checking for NULL (IS NULL / IS NOT NULL)
- 10. Pattern Matching with LIKE
- 11. Using IN for Multiple Values
- 12. Using BETWEEN for Ranges
- 13. Using NOT to Exclude
- 14. Grouping Conditions with Parentheses
- ✅ Summary: WHERE Clause Key Points
- 🔍 Extra Tips: Case Sensitivity in Oracle
1. What is the WHERE Clause?
The WHERE clause is used in SQL to filter records based on specific conditions.
For example, if you want to retrieve only employees who belong to department 10, you’ll use a WHERE clause.
2. Basic Syntax of the WHERE Clause
SELECT column_name FROM table_name
WHERE condition;
3. Sample Table Used (SCOTT Schema)
In this guide, we’ll use the EMP table from the SCOTT schema provided with Oracle Database.
EMP Table DDL and Sample Data
CREATE TABLE EMP (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, DATE '1980-12-17', 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, DATE '1981-02-20',1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, DATE '1981-02-22',1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, DATE '1981-04-02',2975, NULL, 20);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, DATE '1981-06-09',2450, NULL, 10); -- Added row
4. Simple Filtering Condition
Retrieve employees from department 10
SELECT * FROM EMP WHERE DEPTNO = 10;
Command execution example
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
SQL> SELECT * FROM EMP WHERE DEPTNO = 10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
▼ Diagram: Filtering rows with WHERE clause
EMP Table (Partial View)
┌─────┬───────┬───────┐
│EMPNO│ENAME │DEPTNO │
├─────┼───────┼───────┤
│7369 │SMITH │20 │
│7499 │ALLEN │30 │
│7782 │CLARK │10 │★ selected
└─────┴───────┴───────┘
5. Filtering by String and Case Sensitivity
Retrieve employees with name ‘SMITH’
SELECT * FROM EMP WHERE ENAME = 'SMITH';
🔸 Oracle is case-sensitive by default
-- Matches
SELECT * FROM EMP WHERE ENAME = 'SMITH';
-- Does NOT match
SELECT * FROM EMP WHERE ENAME = 'smith';
Command execution example
SQL> SELECT * FROM EMP WHERE ENAME = 'SMITH';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
SQL> SELECT * FROM EMP WHERE ENAME = 'smith';
no rows selected ★No results are output
6. 🔍 Why Using UPPER Ignores Case
Oracle treats 'SMITH' and 'smith' as different values.
To avoid issues with case differences, you can convert both sides of the comparison to uppercase using the UPPER() function.
Example: Case-insensitive comparison
SELECT * FROM EMP WHERE UPPER(ENAME) = 'SMITH';
This will match all of the following:
'SMITH''smith''Smith''SmItH'
You can also use LOWER() if you prefer to compare using lowercase.
SELECT * FROM EMP WHERE LOWER(ENAME) = 'smith';
The key point: apply the same function to both sides of the comparison.
7. Using Comparison Operators
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | WHERE SAL = 800 |
| <> | Not equal to | WHERE DEPTNO <> 10 |
| >, < | Greater/Less | WHERE SAL > 1000 |
| >=, <= | Greater/Equal | WHERE SAL <= 2000 |
8. Using Multiple Conditions (AND / OR)
-- Department 20 and salary ≥ 2000
SELECT * FROM EMP
WHERE DEPTNO = 20 AND SAL >= 2000;
-- Department 10 or 30
SELECT * FROM EMP
WHERE DEPTNO = 10 OR DEPTNO = 30;
Command execution example
SQL> SELECT * FROM EMP
2 WHERE DEPTNO = 20 AND SAL >= 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
SQL> SELECT * FROM EMP
2 WHERE DEPTNO = 10 OR DEPTNO = 30;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
9. Checking for NULL (IS NULL / IS NOT NULL)
-- COMM is NULL
SELECT * FROM EMP WHERE COMM IS NULL;
-- COMM is NOT NULL
SELECT * FROM EMP WHERE COMM IS NOT NULL;
Do NOT use
= NULLor<> NULL— it will not work.
Command execution example
SQL> SELECT * FROM EMP WHERE COMM IS NULL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
SQL> SELECT * FROM EMP WHERE COMM IS NOT NULL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
10. Pattern Matching with LIKE
| Symbol | Meaning |
|---|---|
| % | Any number of characters |
| _ | Exactly one character |
-- Names starting with 'S'
SELECT * FROM EMP WHERE ENAME LIKE 'S%';
-- Names where 2nd character is 'M'
SELECT * FROM EMP WHERE ENAME LIKE '_M%';
Command execution example
SQL> SELECT * FROM EMP WHERE ENAME LIKE 'S%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
SQL> SELECT * FROM EMP WHERE ENAME LIKE '_M%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
11. Using IN for Multiple Values
-- DEPTNO is either 10 or 20
SELECT * FROM EMP WHERE DEPTNO IN (10, 20);
Command execution example
SQL> SELECT * FROM EMP WHERE DEPTNO IN (10, 20);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
12. Using BETWEEN for Ranges
-- SAL between 1000 and 2000 (inclusive)
SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 2000;
Command execution example
SQL> SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
13. Using NOT to Exclude
-- Not department 10
SELECT * FROM EMP WHERE DEPTNO <> 10;
-- or
SELECT * FROM EMP WHERE NOT DEPTNO = 10;
Command execution example
SQL> SELECT * FROM EMP WHERE DEPTNO <> 10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
14. Grouping Conditions with Parentheses
SELECT * FROM EMP
WHERE (DEPTNO = 10 OR DEPTNO = 20)
AND SAL > 2000;
Command execution example
SQL> SELECT * FROM EMP
2 WHERE (DEPTNO = 10 OR DEPTNO = 20)
3 AND SAL > 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
✅ Summary: WHERE Clause Key Points
The WHERE clause allows for flexible row filtering based on many criteria:
- Basic equality and comparison
- NULL checking with
IS NULL - Pattern matching with
LIKE - Multiple values with
IN - Range checks with
BETWEEN - Negation with
NOT - Grouping logic with parentheses
🔍 Extra Tips: Case Sensitivity in Oracle
| Point | Explanation |
|---|---|
| Oracle compares strings case-sensitively | 'SMITH' ≠ 'smith' |
Use UPPER() or LOWER() to normalize text | UPPER(ENAME) = 'SMITH' |
You can also use ENAME = UPPER('smith') | As long as both sides match format |
[reference]
Database Concepts
コメント